Vlookup tricks: the wild side of VLOOKUP

Here is another VLOOKUP post for Bill Jelen’s (now extended) VLOOKUP week.

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

Here is a little-used wild VLOOKUP trick that can sometimes get you out of a hole! And its got a great XIPS rating!

Wild-Card Lookup with VLOOKUP

If you are doing exact-match lookup on text data you can use wild-cards in the Lookup Value.

Suppose you have a table of names and ID Numbers called IDNumbers which looks like this:



will find the first name that contains an n (happens to be Jan-Karel) and returns his ID which is 2345.

The Range-Lookup argument (4th argument) has to be FALSE for this technique to work: sorting the data in wild-card sequence is not yet something Excel can do!

You can use the wild card characters asterisk (*) and question mark (?). An asterisk matches any sequence of characters, including none and a question mark matches any single character.

If you want to match text that includes asterisks and/or question marks you have to precede them with a tilde character (~). So “*~*” would match any string that ended in *

The Wild-Card XIPS rating

Wild-card VLOOKUPs perform reasonably fast. 10 wild-card VLOOKUPS each looking up 500000 rows and not finding a match takes just over half a second single-threaded on my system. Thats a XIPS rating (eXcel Instructions per Second) of 10 x 500000 / 0.5 = 10000000.

If I switch on Multi-Threaded Calculation it takes just 0.149 seconds which is 33.5 MXIPS (Millions of Excel Instructions per second)

33 million comparisons per second can’t be bad.

This entry was posted in Calculation, Lookups. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s