Here are the basics for using the VLOOKUP function in Excel, with a couple helpful tips:
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
VLOOKUP in Excel is described as follows: “looks for a value in the leftmost column of a table, and returns a value in the same row from the column you specify”.
The syntax is =VLOOKUP(lookup_value, table_array, col_index_number,[range_lookup])
The “range_lookup” can either be TRUE – an approximate match, or FALSE – an exact match.
When TRUE, the leftmost column in the table array MUST be in ascending order.
Here are some examples. I have a list of models and I want to look up the location of each model based on a small table in G1:I8):
Here is the formula I will enter. Note that I made the table array range with absolute cell references so that when copied down, the table will be properly accessed. I indicated the column index number as 3 so it will return a value from the 3rd column of the table array. I am also selecting FALSE as the range lookup since I want an exact match:
Once completed and copied down, the results are:
Notice how I’ve ended up with a couple errors. The reason is that, although they look correct, the model numbers in column A that generated the errors actually have spaces either before or after the model:
One way to avoid this is to wrap the cell reference in the TRIM function. Trim will remove all spaces except for a single space between two words. Doing this results as follows:
One other trick you may want to incorporate into your formulas is using a column reference instead of a cell reference. Many power Excel users will do this. It makes it slightly quicker and it also helps when you can’t easily click on the cell because of the proximity of it to your formula. Just click on the column letter instead of the cell reference. Here is the VLOOKUP with this modification:
Now, let’s look at a couple examples using the TRUE range lookup option. In both these scenarios, we don’t have an exact match, but instead are looking up in a range. For this reason, the leftmost column in our table array MUST be in ascending order. Excel will start at the top and look vertically through the leftmost column until the lookup value exceeds the table array, the go back one row, and pull the value from the specified column:
Notice how in both examples we have #N/A errors. That is because the values that produced those errors were below the minimum of the table array. One way to correct this would be to wrap the VLOOKUP formula in an IFERROR function, as such:
This way, if the VLOOKUP formula results in an error, you can specify what you would want the cell input to be.
And there you have it! The basics of VLOOKUP. I hope you find this useful!