## 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:

then

=VLOOKUP(“*n*”,IDNumbers,2,FALSE)

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.