What does it do?
The Excel VLOOKUP function will search vertically, top to bottom, in the left most column of a range of cells to find a matching criterion, if found it will return the corresponding value from that same row and from a specified column within the same range.
Syntax Explained
Lookup_value: This is the value you want to search for in the left most column of your range of cells, it can be a value, cell reference or text string.
Table_array: This is the range of cells (or a named range) where you want to see if your matching lookup_value appears in.
Col_index_num: This will be a value from 1 and up to the number of columns in your range of cells (or named range).
Range_lookup (optional): Enter either 1, TRUE or if omitted to find the closest match, else enter 0 or FALSE for an exact match.
Examples
1. VLOOKUP to match text string exactly.
In the example above, the VLOOKUP function is checking for “Carrot” in column 1 of our table_range (selected in blue), it finds it on row 5 of the spreadsheet. Tracing across manually to column 5 on the row containing “Carrot” we find the value 12, hey presto, we’ve just worked some VLOOKUP magic! 😊 You’re on your way to becoming a pro!
Pro Tip
I personally don’t use TRUE or FALSE in VLOOKUP, if you think that typing a “1” or a “0” saves you oodles of time by making less keystrokes. That gives you more opportunity to invest your time becoming an Excel Ninja!
2. VLOOKUP using approximate match
Personally, I love this example below, I’ve used it many times, both for my own and business use. It really utilises the best out of the approximate match syntax (1, TRUE or omitted) and if you can’t get excited about Excel functions there is no hope for the world! 😉
So, above we have a score and grade system, all nicely laid out in ascending order (column B), which is required in order for this to work correctly. To the right we have scores from students. The function is taking the Score (lookup_value) and finds the closest match in our table_array, or in this case to be clearer, the row which is less than or equal to. Pretty neat huh?
Pro Tip
Excel converts TRUE and FALSE to a 1 and 0 respectively, so you can make a formula in a cell to test this out yourself, try this =TRUE+TRUE. The result will result in a 2. Cool!
Whe#N/All goes wrong!
Eventually you’ll run into error with VLOOKUP, I can guarantee it! As with pretty much all formulation, there is a delicate balancing act you must follow, one wrong parenthesis, a mis-spelt word, a trailing space, missing quotation marks, out of kilter references and the formula will come tumbling down, the list is literally endless!
Here are some common problems using the examples above.
3. Rogue spaces!
Above I’ve demonstrated that B5 does NOT equal B11, this is owing to a SPACE at the end of the word Carrot in cell B11. You can’t see it, this sort of issue can mystify the best of us so I have placed this one first so you can avoid those hours scratching your head!
In the lookup_value above you can see the cursor isn’t quite at the end in one vs. the other. This is where your rogue space is, pesky little thing! Delete it and you shall be granted your VLOOKUP wish!
4. Trying to reference a col_index_num that doesn’t exist in your table.
Above I’ve entered a 9 as the col_index_num, this will throw out a #REF! error since it’s trying to find the 9th column in our table_array of only 8 columns.
5. The lookup_value is NOT in the left most column.
The VLOOKUP function will expect to find your result in the 1st column on the left. This will give you an #N/A result.
That covers the most basic ones your come across, as the VLOOKUP gets embroiled with other function into a beastly formula that does much more it can be harder to find, but I’ll cover that in other linked articles below.