Vlookup - pick last of duplicate entries

robzrob

Board Regular
Joined
Jan 10, 2010
Messages
52
Hello All
Writing this workbook in 2007, but it will be used in 2003. Have searched but can't find answer to this. I have a 5-column (A to E) table array and I'm using VLOOKUP in several other cells to return values in col2, 3, 4 & 5 with my lookup value being col 1. However, col 1 will contain numbers which occur more than once. How can I get VLOOKUP (or something else?) to pick the last occurence (lowest row) and use that one? Also, the array will be sorted - smallest to largest - as the rows go down - how will the SORT work on duplicate entries? I'd like, if possible, for the entry made most recently to be the lowest of the duplicate entries so that VLOOKUP (or whatever) will pick that one to use. Will any kind of SORT do that?
 
If you are only sorting on column 1, then I believe duplicate entries in column 1 will be sorted in the order that they were entered - last entry will be the lowest on the sheet.

If you want to choose the last instance of lookup_value instead of the first one, you can change the FALSE in vlookup() to TRUE.
Excel Workbook
ABCDE
11aa0
21bd1
31c
41d
Sheet1
Excel 2003
Cell Formulas
RangeFormula
D1=VLOOKUP(1,$A$1:$B$4,2,E1)


This, however, will return an answer even if the value is not found. So if your lookup value might not be in the table, then use something else. Maybe this:
Excel Workbook
ABCDE
11a1st formula
21b2eright
31c3ewrong
42d2nd formula
52e2eright
63N/Aright
Sheet1
Excel 2003
Cell Formulas
RangeFormula
D2=VLOOKUP(C2,$A$1:$B$5,2,1)
D5=IF(LOOKUP(C5,$A$1:$A$5)=C5,VLOOKUP(C5,$A$1:$B$5,2,1),"N/A")


Hope that helps.

Tai
 
Upvote 0
To find the last occurrence of the number 99 in column A and return the corresponding column B value, perhaps :-

<TABLE style="WIDTH: 290pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=387 border=0 x:str><COLGROUP><COL style="WIDTH: 290pt; mso-width-source: userset; mso-width-alt: 14153" width=387><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 290pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=387 height=17 x:str="'=IF(ISNA(MATCH(99,A:A,0)),"",INDEX(B:B,MATCH(99,A:A,1),0))">=IF(ISNA(MATCH(99,A:A,0)),"",INDEX(B:B,MATCH(99,A:A,1),0))
</TD></TR></TBODY></TABLE>
 
Upvote 0
To find the last occurrence of the number 99 in column A and return the corresponding column B value, perhaps :-

<TABLE style="WIDTH: 290pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=387 border=0 x:str><COLGROUP><COL style="WIDTH: 290pt; mso-width-source: userset; mso-width-alt: 14153" width=387><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 290pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=387 height=17 x:str="'=IF(ISNA(MATCH(99,A:A,0)),"",INDEX(B:B,MATCH(99,A:A,1),0))">=IF(ISNA(MATCH(99,A:A,0)),"",INDEX(B:B,MATCH(99,A:A,1),0))

</TD></TR></TBODY></TABLE>

This is a similar approach but I think it will be not nearly as fast, given the match(,,0)
 
Upvote 0
What is the formula you have tried?

I was comparing this:

=IF(LOOKUP(C5,$A$1:$A$5)=C5,VLOOKUP(C5,$A$1:$B$5,2,1),"N/A")

to this:

<TABLE style="WIDTH: 290pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=387 border=0 x:str><COLGROUP><COL style="WIDTH: 290pt; mso-width-source: userset; mso-width-alt: 14153" width=387><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-LEFT-COLOR: #ece9d8; BORDER-BOTTOM-COLOR: #ece9d8; WIDTH: 290pt; BORDER-TOP-COLOR: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; BORDER-RIGHT-COLOR: #ece9d8" width=387 height=17 x:str="'=IF(ISNA(MATCH(99,A:A,0)),"",INDEX(B:B,MATCH(99,A:A,1),0))">=IF(ISNA(MATCH(99,A:A,0)),"",INDEX(B:B,MATCH(99,A:A,1),0))


</TD></TR></TBODY></TABLE>
...saying I expect them to do the same thing and the first formula to be faster (providing that you adjusted the ranges).
 
Upvote 0
I was comparing this:

=IF(LOOKUP(C5,$A$1:$A$5)=C5,VLOOKUP(C5,$A$1:$B$5,2,1),"N/A")

to this:

<TABLE style="WIDTH: 290pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=387 x:str><COLGROUP><COL style="WIDTH: 290pt; mso-width-source: userset; mso-width-alt: 14153" width=387><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM-COLOR: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP-COLOR: #ece9d8; WIDTH: 290pt; HEIGHT: 12.75pt; BORDER-RIGHT-COLOR: #ece9d8; BORDER-LEFT-COLOR: #ece9d8" class=xl24 height=17 width=387 x:str="'=IF(ISNA(MATCH(99,A:A,0)),"",INDEX(B:B,MATCH(99,A:A,1),0))">=IF(ISNA(MATCH(99,A:A,0)),"",INDEX(B:B,MATCH(99,A:A,1),0))



</TD></TR></TBODY></TABLE>
...saying I expect them to do the same thing and the first formula to be faster (providing that you adjusted the ranges).

I should have quoted the post I reacted to. It was in fact the original post, not yours. That said, I don't understand the set up that

=IF(ISNA(MATCH(99,A:A,0)),"",INDEX(B:B,MATCH(99,A:A,1),0))

involves. What is 99? Is it a variation on 9.99999999999999E+307? If so, the whole thing can be reduced to:

=LOOKUP(9.99999999999999E+307,A:A,B:B)

Would this compare to:

=IF(LOOKUP(C5,$A$1:$A$5)=C5,VLOOKUP(C5,$A$1:$B$5,2,1),"N/A")

The answer is no... for the obvious reason that the latter is looking up a specific value (i.e., C5).
 
Upvote 0
What is 99? Is it a variation on 9.99999999999999E+307?

No it is a specific value to look for in Column A.

Perhaps I have misunderstood what the OP wants.

Is he looking for the last occurence of a specific value, or merely the last value?
 
Upvote 0
I should have quoted the post I reacted to. It was in fact the original post, not yours. That said, I don't understand the set up that

=IF(ISNA(MATCH(99,A:A,0)),"",INDEX(B:B,MATCH(99,A:A,1),0))

involves. What is 99? Is it a variation on 9.99999999999999E+307? If so, the whole thing can be reduced to:

=LOOKUP(9.99999999999999E+307,A:A,B:B)

Would this compare to:

=IF(LOOKUP(C5,$A$1:$A$5)=C5,VLOOKUP(C5,$A$1:$B$5,2,1),"N/A")

The answer is no... for the obvious reason that the latter is looking up a specific value (i.e., C5).

C5=99, so they are both accomplishing the same thing. I quoted without the context...
 
Upvote 0
This is a similar approach but I think it will be not nearly as fast, given the match(,,0)

How much faster?

How many of these formulas would there need to be before a difference in speed became noticeable?

Why would the match(,,0) bit be "not nearly so fast" ?
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top