Show Last Matched Value in Column

  • Thread starter Thread starter Karl Burrows
  • Start date Start date
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!
 
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
 
Well, that is close. The date range will vary depending on today's date.
Here's my current array formula to look for the occurrences:

{=MAX(IF((BLG!$C$76:$C$118>=$I$2)*(BLG!$C$76:$C$118<=$I$3),(BLG!$J$76:$J$118
),""))}

There are 2 columns: one with a start date and the other with the end date.
If the current date falls within the dates (>=I2 is BOM and <=I3 is EOM),
then it will return the numbers from column J. The problem with MAX is the
second number may not the be the maximum value and therefore, I would get
the wrong number returned.

How do I need to modify this to work?

Thanks!


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
 
Back
Top