## Archive for March, 2012

### VLookup tricks: why 2 VLOOKUPS are better than 1 VLOOKUP

March 29, 2012

OK, time for a post on VLOOKUP for Bill Jelen’s VLOOKUP week.

VLOOKUP Week – Brought to You by Mr. Excel
(Click Image for the Official Site of VLOOKUP Week)

For all you VLOOKUP junkies who can’t get enough of VLOOKUP, here is a wonderful reason to use 2 VLOOKUPs instead of 1 !!

## Exact Match VLOOKUP is slow

When you have a lot of data in Excel (think 10,000 or 100,000 or 1,000,000 rows) you often need to detect when a value you are looking for does not exist in the data.
You can do this using VLOOKUP and the formula would look something like this, supposing you were trying to get information about a Part Number from the 4th column in a Parts List:

=VLOOKUP(PartNumber,PartsList,4,FALSE)

If Part Number is not in the Parts List then the formula will return #N/A. The final VLOOKUP argument (FALSE) tells VLOOKUP to do a linear exact match search of the Parts List, starting at the first row and looking at each row in turn until it finds the Part Number or reaches the end of the Parts List.

This works well with a small number of VLOOKUPs and when Parts List has a small number of rows, but for large numbers of VLOOKUPs with a large PartsList it can take minutes to calculate.

This problem is made even worse by the fan-out effect of making a change to the Parts List table.
Because of the way that Excel’s smart recalculation engine tracks dependencies, if you make a change to a particular row the Parts List table ALL the formulas that refer to Parts List will be recalculated, even if the result of the formula does not use the row that was changed.

## So how do you speed up this slow calculation?

If you sort the PartsList table on Part Number then it would be nice to be able to use a formula like this:

=VLOOKUP(PartNumber,PartsList,4,TRUE)

The TRUE as the final VLOOKUP argument tells VLOOKUP to do a binary search of the parts list.

Binary Search is lightning fast compared to the linear search.

But it does not care about missing data: if the PartNumber does not exist VLOOKUP will happily find the next largest part number that is less than Part Number.

And it won’t tell you that it could not find the Part Number, so its extremely difficult to spot that you have got the wrong answer!

Thats why many people say you should never use the TRUE argument in VLOOKUP.

## VLOOKUP has a major design error

Oh and by the way TRUE is the default if you don’t enter anything for the fourth argument.
So if your data is not sorted, or you have missing data, and you forget to add the FALSE argument you will get the wrong answer.

## The magic solution: 2 VLOOKUPs are better than 1

OK, so here is the magic trick you can use to make safe fast VLOOKUPs on sorted data, even when there may be missing or mismatched data.

Suppose you use VLOOKUP to lookup the Part Number and return itself from the Part Number Column (column 1) in the Part Number Table.

=VLOOKUP(PartNumber,PartsList,1,TRUE)

If the PartNumber exists this formula will return the same Part Number as the PartNumber you gave it.

But if the PartNumber does not exist the formula will give you a different part number!

So we can use IF to find missing Part Numbers:

=IF(VLOOKUP(PartNumber,PartsList,1,TRUE)=PartNumber, “Exists”, “Missing”)

And so for the final magic formula you just replace the “Exists” with the VLOOKUP for the data you want!

=IF(VLOOKUP(PartNumber,PartsList,1,TRUE)=PartNumber, VLOOKUP(PartNumber,PartsList,4,TRUE), “Missing”)

Because VLOOKUP binary search is so much faster than VLOOKUP linear search this trick is faster even with small numbers of rows (50 or so).

## More LOOKUP Tricks

My website has a page showing many tricks for using Lookups:

http://www.decisionmodels.com/optspeede.htm

And there are 2 earlier posts in this blog on Lookups:

http://fastexcel.wordpress.com/2011/07/20/developing-faster-lookups-part-1-using-excels-functions-efficiently/

http://fastexcel.wordpress.com/2011/07/22/developing-faster-lookups-part-2-how-to-build-a-faster-vba-lookup/

### 2012 MVP Summit

March 4, 2012

Every year Microsoft hosts the MVP Global Summit at the Microsoft campus in Redmond. Over 1500 MVPs from all around the world meet up with the Microsoft Development teams to discuss whats new and to share technical solutions and problems with each other.

The Microsoft Campus is a collection of buildings (over 70?) sprawled over a large site in Redmond. Fortunately they run an excellent bus service fanning out from the central transportation hub to the buildings.

There was a good turnout of Excel MVPs (about 25 out of the worldwide total of 85) and it was great to meet up with both old acquaintances and new Excel MVPs. We spent the week discussing amazing things like (redacted, redacted, redacted and redacted) with the Excel dev team, and having a great time hanging out at the Excel favourite place (Rockbottom).

Anyway enough chat: here are some snaps -

Left: Roger Govier, Bob Umlas, Brad Yundt Right: Andy Pope, Ken Puls, Bob Phillips, Jan Karel Pieterse, Dick Kusleika, Roger Govier

Ken and Dee Puls, Tom Urtis

Brad Yundt and Frederic Le G

Jan Karel Pieterse, Katherine and Bill Manville, Dee Puls,
Andy Pope, Ken Puls, Mike Alexander, Jon Peltier, Dick Kusleika, Jerry Latham

uen

Ingeborg Hawighorst (Teylyn) , Andy Pope and Ken Puls

Zack Barresse watches Jan Karel Pieterse in action

Ken Puls, Mike Alexander, Roger Govier, Andy Pope, Bob Phillips, Jon Peltier, Dick Kusleika, Patrick Matthews

Kevin Jones (Zorvek) , Chris "Smitty" Smith, Zack Barresse, Jan Karel Pieterse, Mike Alexander

Raising a glass in memory of Nate Oliver: Crystal Long, Duane Hookam, Jane karel Pieterse, Mike Alexander

### And finally the Photo Quiz: Where exactly is this famous landmark situated?

Yes it IS this way up.