In this Excel Tutorial I am going to discuss Excel 2010 Vlookup Function. During this Tutorial I will focus on Lookup Value argument and reasons why it's not working.
In this tutorial I will cover why Vlookup might not be working and in future tutorials I will cover in details how to deal with that.
For the purpose of this Excel Tutorial I will be using the same template like in previous part 1. It will be Daily Sales report.
I have got column Description with Vlookup that references the column Product Number/Sku Number. It references table inventory and it gets the value from the second column with the range Lookup zero (0) which is exact match.
I will go to Inventory where is my table. First column and the second column.
In the first example I will covers situations where you can get non applicable (#n/a) error. I will come back to Inventory column and remove first value 90001 - reference table.
When you come back to the Sales Data table you can see that we get #n/a (Non Applicable). That mean that the value is not found in the Inventory table (because we removed it).
If I come back to the Inventory table to put the value back and come back to the Sales Data table we can see we get the Description.
Let's cover another example where I will modify the input value on sales data sheet.
First I am going to remove the validation from my field. To do this I will go to Data Validation which is in Data field, click on it and select Data Validation.
In Validation Criteria we have an Allow - where I select Any value.
Now I can put any value so I will put - a90023 and I will go to the Inventory table and do the same in the first column product number - a90023.
The result should be fine but let's check what will happen when I will put space at the end. We get #n/a (Not Applicable). It cannot find it because I put space at the end. You have to make sure that the values you use are clean and you can use some Excel function to clean them like function trim.
Another reason why the function might not be working is when our key column in our table has a space. I will add a space in Inventory Sheet and when I come back to the Sales Data there is the same problem like in previous example #n/a.
These are the common reasons why you can get #n/a (not applicable). Another common question is what will happen if you have got duplicate data.
For this example in the Inventory table I will select first value - 90001 and I will duplicate it. I have two rows with the same product number (key) if we use Vlookup with value 90001 what will be the result of the function: blanket or pillow?
In sales Data in description column I have blanket value so we get this value instead of Pillow.
I will come back to the Inventory tab and swap the value ( blanket, pillow ). In the second value I will put blanket.
In the Sales Data you can see that Vlookup changed the value to Pillow.
The logic it uses is that it searches for the value from top to bottom and when it finds the first value then it returns without scanning the next item. This is how it handles duplicates; it will only take it from the first found value (top to bottom scan).
Another challenge with Vlookup Lookup Value argument is how to use it when a table where the column we want to search on is a not first column in the table which is a requirement and we will have to either move the column to the beginning or ad extra column at the very beginning and use a formula to reference key column and I will cover that in more details in another vlookup tutorial.
I hope this Excel Tutorial was helpful.
Katie and Emil