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.
