Advanced VLOOKUP





The VLOOKUP function is, IMHO, one of the most used functions in Excel. Even so, spelling mistakes and extra spaces can throw it off completely. Here’s my simple solution for dealing with extra spaces.

Tip #1)
N.B. Intentional extra spaces have been put before, in the middle, and after each name:

Instead of:
Formula:
=VLOOKUP(D2,A1:B4,2,0)

You can use:
Formula:

=VLOOKUP(TRIM(D2),TRIM(A1:B4),2,0)

entered as Ctrl+Shift+Enter

Tip #2)
Now you might only know one name (e.g. John or Doe) but not the other name (ie. partial match). Then you can use a little semi-fuzzy logic to get the answer:

Formula:

=VLOOKUP(“*”&TRIM(D3)&”*”,A1:B4,2,0)

N.B. The above formula assumes that you are only looking up one word (e.g. no spaces).

If you are looking for a partial match with more than one word (e.g. Joh D) then use this formula:
Formula:

=VLOOKUP(“*”&TRIM(D2)&”*”,TRIM(A1:B4),2,0)

entered as Ctrl+Shift+Enter

Tip #3)
If you are having problems with spelling mistakes, etc. then you need to get really fuzzy. Alan has provided some awesome code for doing Fuzzy-VLookup including Soundex and Metaphone similarities.

If your boss is against using non-Microsoft add-ins or vba macros/functions then you can rest assured that MS has jumped on the bandwagon and now has its own version of Fuzzy matching for VLookup. You can find the add-in here, but be warned that it only works on tables and only officially with Excel 2010. Debra has created a page on this topic and one of the comments posted is that it also works on Excel 2007. Haven’t tried it except on Excel 2010 so can’t comment.

Tip #4)
One of the greatest limitations of VLookup is that is only looks up in one direction (to the right) of any said value. Looking at the example below, if you wanted to use an account number to find out the rest of the details of a purchase you woud not be able to do so with the traditional vlookup formula since the account numbers are not the first column in the lookup table.

This tip is actually two in one:

Part 1. If you only wanted to know one piece of information that is related to your lookup value then you can use a combination of Choose and VLookup:

Formula:

=VLOOKUP(F2,CHOOSE({1,2},B2:B4,A2:A4),2,0)

this tricks vlookup into switching the columns around and so finds the account number in Column B and returns the associated value from Column A. This technique is detailed here (http://www.myonlinetraininghub.com/e…t-using-choose).

Part 2. Often this is not enough and you would like to be build a complete retrieval system for the information in the VLookup table as you would with a traditional VLookup formula. This can be accomplished by taking the above formula one step further by creating a nested VLookup formula where the inner VLOOKUP will provide the lookup value for the outer VLOOKUP. If anyone knows of any references for this I will be happy to post it as I actually thought of this by myself (not saying I was the first, but I haven’t seen anyone else use this technique before).

Names:
Formula:

=VLOOKUP(F2,CHOOSE({1,2},B2:B4,A2:A4),2,0)

Order details:
Formula:

=VLOOKUP(VLOOKUP(F2,CHOOSE({1,2},B2:B4,A2:A4),2,0),A2:D4,3,0)

Order status:
Formula:

=VLOOKUP(VLOOKUP(F2,CHOOSE({1,2},B2:B4,A2:A4),2,0),A2:D4,4,0)



One thought on “Advanced VLOOKUP

  1. I am interested in Tip 2 to solve my problem. Col A has names and Titles in it. Ex [LName],[Title], [FName]. Titles repeat, one to many. In Column E, I wanted certain titles inserted; otherwise leave blank. Is there a VLOOKUP formula that will handle that? Many Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *


1 × three =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>