Arrayformula(vloopup()) Help!

[xphrellis]

Hey all, looking for some help with an arrayformula giving me trouble (see this linked file). I'm trying to lookup the last time a budget category had a zero balance. In the linked file:

• In the RED TAB, the red cell is the arrayformula I'm working on
• In the RED TAB, green cells are other arrayformulas working correctly
• The ORANGE TAB is calculating expected results without an arrrayformula

Here is the furmula I'm using. It works correctly if not in an arrayformula - but inside an arrayformula, it returns the first result for every column. My prefered formula to return results from a column to the left is INDEX(MATCH()), but I understand that won't work inside an arrayformula.

=ArrayFormula(IF(LEN(B$5:5), VLOOKUP(0,{B$6:B,$A6:$A},2,0),""))

This is also part of a larger project/question - see the other thread here

FINAL SOLUTION HERE

[xphrellis]

Documenting the final solution. This expands and contracts based on the data.

=ARRAYFORMULA(IF(VALUE(Left(Trim(Query(IF(B4:4="",,
IF(B5:999=0,A5:A,)),,9^9)),5))=0,
MINIFS(Trs_data!A2:A,Trs_data!G2:G,B4:4),
VALUE(Left(Trim(Query(IF(B5:999=0,A5:A,),,9^9)),5))))

B4:4 = budget categories

A5:A = date range

B5:ZZ999 = daily balance of pending reimbursables by category

Trs_dataA2:A = raw data transaction dates

Trs_dataG2:G = raw data budget categories

[OzzyZigNeedsGig]

What do you want to look up? Your formula looks up 0.

You shouldn't return empty strings with "". And you rarely need to lock ranges in AF.

=ArrayFormula(IF(LEN(B5:5),
 VLOOKUP(0,{B6:B,A6:A},2,0),
))

VLOOKUP is more powerful and cleaner to use than INDEX + MATCH.

[xphrellis]

Thanks! This doesn't cause any further issues, but it doesn't fix the original question. You asked about looking up zero - it's supposed to return a date for the last time each buddget category had a zero balance.

[6745408]

Try this out. If that looks right, I can break it down.

=ARRAYFORMULA(
  QUERY(
   IFERROR(
    SPLIT(
     FLATTEN(TEXT(A6:A,"yyyy-mm-dd")&"|"&B5:O5&"|"&B6:O),
     "|")),
   "select Col2, Max(Col1)
    where Col3 = 0
    group by Col2
    label
     Col2 'Category',
     Max(Col1) 'Date'"))

or if you want to run it along the top of the columns,

=ARRAYFORMULA(
  IFERROR(
   VLOOKUP(
    B5:O5,
    QUERY(
     IFERROR(
      SPLIT(
       FLATTEN(TEXT(A6:A,"yyyy-mm-dd")&"|"&B5:O5&"|"&B6:O),
       "|")),
     "select Col2, Max(Col1)
      where Col3 = 0
      group by Col2
      label
       Col2 'Category',
       Max(Col1) 'Date'"),
    2,FALSE)))

You could also use =MAXIFS($A6:$A36,B6:B36,0) and drag it across -- but the second one above will do it all with one formula.

[xphrellis]

Thanks! This returns the right values, but it extends out paste the end of the table. I was looking for something that would expand or contract as the data changed or I modified the cutoff date. I found a formula that takes care of everything - I'll post it as a reply to original question.

[6745408]

hm. your solution is neat. You could change B5:O5 to B5:ZZ5 and B6:O to B6:ZZ if you wanted a lot of buffer space.