VLookup is one of those functions that can be incredibly useful once mastered. However, while it seems simple to use, there are a few simple errors that people make which lead to giving false results. This article aims to clarify the pitfalls and provide you with knowledge of how to use this feature of Excel. As you probably already know, there are many ways of entering a formula into a cell in Excel.
- You can write the formula from scratch in a cell.
- you can write a formula in the formula bar.
- You can use the Insert Function Tool.
- You can start to type the formula and utilise the suggestions that appear.
When you create the VLOOKUP function in your spreadsheet, you can find an exact match or a close match by either typing in True or false in the Range lookup section.
An example of a Vlookup formula
Here is a sample data list. 
Here is the list which I shall use for the Vlookup function to allocate the correct unit price for each product. Which I shall name for ease of use [UnitPrice].
Remember that the first column needs to be sorted in alphabetical order for this to work. This is explained in a previous tip How to name cells or ranges in Excel.
Using the Insert Function Tool that gives you a Dialog box to fill in, you may see something like this; a partially filled-in dialogue box with most of the formula, where the Range Lookup is empty. 
B2 refers to the cell with the condition you are looking for in the named range.
Unit price is the named range that has the answers.
2 is the number of columns from the left most in the named range where the answers you need are held.
With your cursor now in the [Range_Lookup] area, the explanation at the bottom of the dialog box tells you that if you type the word [TRUE], or leave this field blank, you will find the closest match to your lookup value.
If you type in [FALSE] then Excel will look for and only return an answer if it finds an exact match to your criteria. i.e. Only the Lookup value.
The Problem with VLOOKUP
BUT, if you were to type the formula manually into a cell, by not using the tool, and you type in a final comma but nothing after it,
believing that the range lookup field is left blank and therefore should return the closest match. You will be surprised at the result.
Lets go back to the sample sheet, where I have the products, store, part number etc. In column F, I will need the Unit price to be places from the named range Unit Price. I am using the Insert Function Tool to create a Vlookup formula in the cell F2
Step 1 
- In the Lookup_value line: Select the cell B2 as the reference item to seek in the range named [Unit Price]
- In the Table_array line: Press [F3] key to see the list of name ranges you hold in this workbook and select [UnitPrice]
- In the Col_index_num line: Type in the number 2 to retrieve the number from the second column in the table array.
The result should look like this.
Step 2
I have purposely left the Range_lookup line empty.
- Select the [OK] button.
- Autofill the result in the cell F2 down the page and the result should look like this.
For this demonstration only, I shall now copy the results in column F and paste them as values into column H, for later comparison. 
When it goes wrong
I shall now overtype the formula into the cell F2. 
As I start to type, Excel tries to help by presenting me with the tip. Typing and following the tip as I go this is what you see. 
When I type in the closing bracket, I see this.
For ease of demonstration I have purposely changed the original data of a few entries in column B, to show what happens when I used this last formula which I typed in. I have replaced some of the words “Keyboard” with just “Key” and “Mouse” with “M”. As soon as I do the formulae in column F reflect this.
But wait! Doesn’t the range lookup line if left empty mean that I get a result of anything like the lookup value? Meaning that K is close enough to Keyboard to give the same result. Surely!
No because of the way you have input the formula! 
NOW
Return to the formula and type in True for the last entry after the last comma. 
The quickest way to update a formula is to select the cell with the formula in and select the fx tool in the formula bar. 
This takes you to the function dialog box even if you did not use it to create the formula in the first place.
- Autofill the formula down the list.
The result is this. At first you might think this is correct. No glaring mistakes.
I have highlighted the wrong results. But what a result. K is the same price as Hard Drive and M is the same as a Keyboard. Completely wrong!
Exact match
Now correct the final part of the formula from True to False the get an exact match only.
You get the right answers.
This tip has highlighted that you need to be careful how you input your formulae. Typing is great when you know what you are doing and sometimes is far quicker than the mouse, but in this case, it has caused a problem that would not have been easily picked up until a customer complained. And we have all been there I’m sure to an answer from a salesperson. “well it must be that. The computer says so!”.
Look out for my next tip on how to show a more meaningful result than #N/A.
