In this Excel Tutorial I am going to discuss Excel 2010 Vlookup Function.
During this Excel Tutorial I will focus on Keeps Returning #n/a which is a common problem while working with Vlookup function.
In this Tutorial I will show you example when #n/a can happen and how to deal with that.
First I will go to the Inventory table. Next I will copy the whole table and put it to the separate sheets.
First I am going to type a number and next to Vlookup function. To do this I type equal and select Vlookup. Next I will select the number 90001, refferend the table array which is the entire table - I select table.
Next I would like to returnee the second column with Exact Match - which is 0 or false.In my case it will be 0. I close parenthesis.
For this example my result is Pillow which is correct.
For the first example I will show you how it deals with text. I will put an apostrophe (') at the beginning which will indicated - this is text not a number. The result is that we get #N/A also we have exactly the same number at the left side. The difference is that the number at the left side is treated as a number but at the right side as a text. For excel this are two different values.
Apostrophe is often use to handle search and issues with Excel during import and export. There might be reasons why it used. When we have got a problem and we want to remove it we can either remove it manually or we can use a method when we multiplite the values by one. I will show you example of that.
I have got several values with an apostrophe and I am going to put number one on the left side and copy it and select several values. Next I will go to Paste option and select Paste special.
I will Multiply it by one. Multiply by one won't change the actually value however it will move apostrophe.
Another common problem is when we deal with text. I will put an a at the beginning of value in cell 1A. When we check value F1 90001 it doesn't match so I will put a the same like in previous step in cell F1. Pictures below show you this steps.
Now I will show you what if I put space at the end of number value - It doesn't match. With the spaces at the end, beginning or special characters then Excel might not represent and will appear as a kind of a space but it not a real space. Vlookup will return #N/A.
One way to deal with that - is removing a space manually. Another way is find and replace however this may remove space in the middle - if you don't have them that may work.
Different way to do that is using some kind of function. There are a number of function that can deal with Data quality issues. One of this Function is Trim - what will remove spaces at the beginning, at the end and also spaces in the middle if there are some more that one space. I will remove space using Trim Function.
You can also use another function like clean - which can remove special characters.
We can get a space in our table number - but that doesn't work.
There is a way how you can do this. You can introduce a separate table, specify to cleans our values. At the picture below I show you this step.
You can reference this table in the Vlookup to fix it or you can just use this value and over right the existing value. Next go to the Paste option, select Paste special and in the table Values. Click Ok. This should change your value from #N/A to the value before in my case Pillow.
Another example is when we deal with different Data types. In the text ( which I show you below )
We have text in both cases. I will remove a from F1 and next remove a from A1.
When I remove a both treated as a number but I get #N/A. The reason why I get #N/A which worked before is because I used Trim function. Trim function will return a text value. That means that a text value is different from the same value which is treated as a number.
I will remove Trim function returns text and previous value should be on this cell without #N/A.
Another common problem when we have #N/A is when we deal with table array. What we might get when I move it one a lower and move a Blanket value lower. You will get #N/A because table references changes.
This is how Vlookup function keeps returning #N/A working.
I hope this Excel Tutorial was helpful.
Katie and Emil.