Vlookup has been a leading feature of Microsoft Excel for a long time. For many users, its the first function they think of when opening a spreadsheet. Excel has several types of Lookups but VLOOKUP is the most commonly used of the bunch.
Let this page be your one-stop shop to making the most of VLOOKUPs. All the VLOOKUP fundamentals are here along with some additional VLOOKUP pro tips.
Lets Check out the basic syntax of the VLOOKUP statement:
=VLOOKUP(“Value to Match”, “Range for Return Value”, “Return Value Column”, “Exact Match(TRUE/FALSE)”
=VLOOKUP(A6,E5:F10,2,FALSE)
In the above example, you are trying to find A6 in the range E5 to F10. When the matching value is found in that range, it will return the corresponding value from column 2. FALSE indicates that you will not accept the closest match, exact matches only.
VLOOKUP Best Practices
Set the Exact Match Parameter to FALSE
The Final column defaults to True which means, by default your VLOOKUP will return approximate matches. Unlike playing horseshoes, partial matches are almost never useful in Excel so make sure to set this to FALSE.
Be Absolutely sure it works
…with absolute references. Absolute References ensure that you are accessing the same Range for all of the values you are looking up. It’s particularly useful when you copy down your lookup formula. Most of the time you don’t want the Return range to slide down along with the values you are searching for.
=VLOOKUP(A6,$E$5:$F$10,2,FALSE)
The dollar-dollar bills in this situation mean to lock the column( when preceding a column letter) and/or lock rows(when preceding a row number).
Dig deeper into cell references here!
Insert Formatted Fields for your Matching Column
Poor VLOOKUP could never make it as a male model. Not only because it is a function in a software application rather than a person, but it, unfortunately, can’t turn left.
Since VLOOKUP only can summon values to its right, I suggest you add a column for VLOOKUP to use. Make that column number one. Or column A I should say!
Furthermore prevent whacky number issues by converting the values to formatted text. I prefer converting to text but if you must keep numbers, make sure you are comparing to numbers of the same format.
Here’s the formula you can use to match your values.
If both the column you are pulling and matching against have this format you shouldn’t have any problems.
= TEXT(Value, “000000”)
= TEXT(A6, “000000”)
Get a Handle on Those Errors
IFERROR(VLOOKUP STUFF), “Value if you get an Error”).
I usually put return 0 for errors or the word “FALSE” but you can put anything you want. Go ahead put “Garbage” or even some profanity in there if you are really afraid you will miss an error.
Just don’t forget to remove that nonsense before you send the report to the boss.
Better yet, use conditional formatting to make the Non-Matching cells really pop.
=IFERROR(VLOOKUP(A6,$E$5:$F$10,2,FALSE), “No Match”)
VLOOKUP Quick Tips
Now that we have a solid VLOOKUP foundation, let’s add some tips to make you, even more, PRO with VLOOKUP.
Here are some other tips to make you even more proficient with VLOOKUPS.
Quick Count to Identify Column Number
One annoying characteristic about VLOOKUP is that you need to explicitly spell out the column you are trying to retrieve data from.
It’s not a big deal when you are working with only a few columns but when the dataset gets larger, it can be cumbersome.
Next time try this:
Start by selecting the cell you want to return a value from.
Hold Control, Shift, and the left arrow key to highlight a ranged selection of data. Now note the count in the bottom right-hand corner. You can enter this number into your VLOOKUP as the “return value column” and you are ready to go.
Note that the feature is a count so if one of the cells contains a blank value it throws the whole thing off. Biggest time saver since you learned to stop counting on your fingers!
Many more VLOOKUP tips to come, this will be a running list!
