Vlookup - pick last of duplicate entries

[robzrob]

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?

 

[taigovinda]

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
	A	B	C	D	E
1	1	a		a	0
2	1	b		d	1
3	1	c
4	1	d
Sheet1

Excel 2003

Cell Formulas
Range	Formula
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
	A	B	C	D	E
1	1	a	1st formula
2	1	b	2	e	right
3	1	c	3	e	wrong
4	2	d	2nd formula
5	2	e	2	e	right
6			3	N/A	right
Sheet1

Excel 2003

Cell Formulas
Range	Formula
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

 

[Boller]

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>

 

[taigovinda]

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)

 

[Aladin Akyurek]

What is the formula you have tried?

 

[taigovinda]

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

 

[Aladin Akyurek]

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

 

[Boller]

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?

 

[taigovinda]

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

 

[Boller]

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" ?