Index Match to return last value

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