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]

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

The match(,,0), as I'm sure you know, does not rely on the sorting of the numbers. By looking for an exact match, it is (on a % basis) much slower.

A couple of times, I've had files where I needed to perform thousands of lookups and my table was close to a million rows. In that case, using lookup()=value instead of match(,,0) saves quite a few minutes.

You're right that in this case and in most cases, you probably won't care or notice the difference. But I bet it is 100x as fast to use 1 instead of 0.

 

[robzrob]

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

Thanks - will try it.

 

[robzrob]

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" 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: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 290pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #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>

Thanks - that worked fine.

 

[robzrob]

What is the formula you have tried?

Boller's worked.

 

[Boller]

The match(,,0), as I'm sure you know, does not rely on the sorting of the numbers. By looking for an exact match, it is (on a % basis) much slower.

A couple of times, I've had files where I needed to perform thousands of lookups and my table was close to a million rows. In that case, using lookup()=value instead of match(,,0) saves quite a few minutes.

You're right that in this case and in most cases, you probably won't care or notice the difference. But I bet it is 100x as fast to use 1 instead of 0.

I would be interested to know what the difference is.
Do you know of any sources that have results based on tests of actual data (I'd prefer to avoid trying to come up with a test myself).

 

[taigovinda]

I would be interested to know what the difference is.
Do you know of any sources that have results based on tests of actual data (I'd prefer to avoid trying to come up with a test myself).

Unfortunately I don't - and I've never done any time tests myself, so I wouldn't trust my results if I did try to test it.

This is probably an obvious point, but my guess is that the % difference between match(,,1) and match(,,0) grows with the size of the lookup table. Like, one is N and one is logN or something along those lines.

 

[Aladin Akyurek]

Lookup functions with the match-type set to 0 do a form of linear search. When the match-type set to 1 and LOOKUP by default do a form of binary search. You can get efficiency scores if you do a search on these search types, in particular in computer science circles.

By the way, if a table say A2:B60000 is sorted on its first column (housing text values)...

=IF(LOOKUP(X2,$A$2:$A$60000)=X2,LOOKUP(X2,$A$2:$A$60000,$B$2:$B$60000),"")

=IF(VLOOKUP(X2,$A$2:$A$60000,1,1)=X2,VLOOKUP(X2,$A$2:$B$60000,2,1),"")

will be significantly faster than

=VLOOKUP(X2,$A$2:$B$60000,2,0)

You can find info about the foregoing at www.decisionmodels.com.