Index or HLookup working "alone" but not working inside ArrayFormula

user_19371526 0
Viewed 31

I'm trying to put the following formulas inside an ArrayFormula but the result is wrong (keep providing the first row result):

Working in each single cell

=INDEX($D$2:$AKA;match($A2:A;$A$2:A;false);MATCH(DATE(YEAR(TODAY());MONTH(TODAY());1);$D$1:$AKA$1;false))

Showing the same result in all cells

=arrayformula(IF(LEN($A$2:A);INDEX($D$2:$AKA;match($A2:A;$A$2:A;false);MATCH(DATE(YEAR(TODAY());MONTH(TODAY());1);$D$1:$AKA$1;false));))

Expected/Results

Gsheet example

Thanks in advance, Nick

vlookup array-formulas google-sheets match arrays
2 Answers

use:

=INDEX(IFERROR(HLOOKUP(EOMONTH(TODAY(); -1)+1; D1:1000; IF(A2:A="";;ROW(A2:A)); 0)))

enter image description here

user_56326290

You may also try QUERY() approach.

=QUERY(INDEX(D2:5000;;MATCH(EOMONTH(TODAY();-1)+1;D1:1));"limit " & COUNTA(A2:A))

enter image description here

user_55147470
Related