Show Last Matched Value in Column

K

Karl Burrows

Hi!

I have several arrays that look at a date range and then return the values
for that date from another column. The problem is the date range may appear
twice. I need to only show the last one found in the array. Any help?

Thanks!
 
P

Peo Sjoblom

Assume you want to look for 01/10/04 in A2:A30, it occurs twice
and you want to return a value from B2:B30, you want to return
the second occurrence counted from A2 down

=INDEX($B$1:$B$30,MAX(($A$2:$A$30=DATE(2004,1,10))*ROW($A$2:$A$30)))

entered with ctrl + shift & enter

You can of course replace DATE(2004,1,10) with a cell where you put the date
criteria

Note that I use B1:B30 in the index, that is because row* will return the
match
counted from row 1 so you either have to offset it or use index from row1
 
Top