Lookup function

[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.

 

[Don Guillett]

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

 

[Alan Beban]

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

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

Alan Beban

 

[Domenic]

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!

 

[Alan Beban]

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

Alan Beban

 

[Harlan Grove]

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

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)))

 

[Domenic]

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

Alan Beban

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.