Lookup function

  • Thread starter Thread starter zak
  • Start date Start date
Z

zak

I am trying to lookup a date in a list of serial# and
dates. My Problem is that I have more than 1 duplicated
serial# in the list and only want the newest entry to be
returned.
 
Use match
=match(1,b:b) will find the last 1 in the column
then use the match within an INDEX function instead of lookup
 
Don said:
Use match
=match(1,b:b) will find the last 1 in the column
then use the match within an INDEX function instead of lookup
Click to expand...
The above doesn't seem to return the last 1 in Column B, at least not
consistently.

Alan Beban
 
Hi Zak,

Assuming that your serial numbers are in Column A, your dates are in
Column B, and D1 contains the serial number you're using for your lookup
value, try:

=INDEX($B$1:$B$5,MATCH(MAX(IF($A$1:$A$5=D1,$B$1:$B$5)),$B$1:$B$5,0))

to be entered using CTRL+SHIFT+ENTER. Adjust the range as needed.

Hope this helps!
 
This formula assumes that the newest date entry (presumably the entry in
the highest numbered row) is also the latest date.

Alan Beban
 
The above doesn't seem to return the last 1 in Column B, at least not
consistently.
Click to expand...

True. You'd need to use

=MATCH(1,1/(B1:B65535=SoughtValue))

which needs to be entered as an array formula, and that means you can't refer to
entire columns. If entire column searches are needed, try the array formula

=IF(B1=SoughtValue,1,1+MATCH(1,1/(B2:B65536=SoughtValue)))
 
Alan Beban said:
This formula assumes that the newest date entry (presumably the entry in
the highest numbered row) is also the latest date.

Alan Beban
Click to expand...

It looks like I misread the post. I thought the OP wanted the latest
date for a serial number, but after re-reading the post, I see it's the
latest entry.

With that in mind, the following formula should work:

=INDEX(B1:B100,MAX(IF((A1:A100=D1),ROW(A1:A100))))

entered using CTRL+SHIFT+ENTER.

where D1 contains the serial number used as the lookup value.
 
Back
Top