## 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

TRUEorFALSEinVLOOKUP, 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

TRUEandFALSEto a1and0respectively, so you can make a formula in a cell to test this out yourself, try this=TRUE+TRUE. The result will result in a2. Cool!

## Whe**#N/A**ll 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 9^{th} 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 1^{st} 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.