Index Match to return last value

Hi Guys Im new here and was hoping someone could help me

I would like to edit the following formular to return the last found value in colum A:A instead of the first value
apparently this is an array formular (not quite sure what that means), but basically it is only returning my value if all 3 criteria is met

I would still like it to retun my value only if all 3 criteria is matched, but the difference being the last found value.

My lookup ID's are numerical like this 11001, 11002, 11003 ect, my data is not sorted which is why I chose index match

{=IFERROR(IF($A4<>"",VALUE(INDEX(YTD!$A:$X,MATCH(1,(YTD!$A:$A=$A4)*(YTD!$F:$F=$B4)*(YTD!$H:$H=$C4),0),24)),""),"")}


Thanks all in adavance

Hi,

I would like to propose a LOOKUP(2 ...

the new formula segment could be (instead of INDEX...

LOOKUP(2,1/((YTD!$A1:$A1000=$A4)*(YTD!$F1:$F1000=$B4)*(YTD!$H1:$H1000=$C4)),YTD!X1:X1000)

Just an attempt

Hi, Try this array

Formula:

=INDEX(YTD!X1:X1000,LARGE(IF(YTD!A1:A1000=A4,IF(YTD!F1:F1000=B4,IF(YTD!H1:H1000=C4,ROW(YTD!X1:X1000)))),1),)

Hi welcome to this forum...
Try this.. Array entered formula...

Formula:

=INDEX(YTD!X1:X1000,MAX(IF((YTD!A1:A1000=A4)*(YTD!F1:F1000=B4)*(YTD!H1:H1000=C4),ROW(YTD!X1:X1000))),)

Hi,

your formula looks great to manage 3 columns criteria. LOOKUP(2... is a different tecnique to approach the problem.

INDEX( LARGE or INDEX(MAX confirmed with control+shift enter are very good approaches too:

Maybe a small sample file could help to fine-tune the formulas.

Regards

Hi, thanks for your kind feedback


This segment:

(YTD!$A1:$A1000=$A4)*(YTD!$F1:$F1000=$B4)*(YTD!$H1:$H1000=$C4))

will return an array of either TRUE or FALSE: TRUE only when thee conditions are met. The division operations convert those logical values to numeric values and errors.

1/TRUE = 1

1/FALSE = #DIV/0!

LOOKUP reads an array of 1s and #DIV/0! errors. 2 is the lookup value. LOOKUP is searching a 2. Now, if the lookup value is greater than any number in the array of 1s and #DIV/0! errors, then the formula will look for (match) the last number in the array that is less than the lookup value (2).

The only and the largest number produced by formula in the array is 1, so every number in the array (1,error,1,1,error) is less than the lookup value of 2 and returns the corresponding result as expected. LOOKUP ignores and skips error.

Same results if you change 2 with every number greater than 1.

Cheers