Reasons for VLOOKUP returning #n/a
The most common problems with VLOOKUP occur when you are expecting to see a value but see a #n/a error instead. The are several situations that may lead to this.
#N/A when you are using an Exact match (FALSE as the last option)
The simple answer is that Excel cannot find the value you are looking for in the list you provided. However, the reason for this may vary:
- It might not be there- Excel may be correct and it may not be in the list. You should look in your table and see if you can see the lookup value yourself. If you can then it must be one of the reasons below. If you can’t then Excel is probably correct. As shown below, the student A150 does not exist in the table.
- The lookup value and first column in the table array are not the same. Most common reasons for this are:
- spaces before or after words. Check for this first as it is very hard to see. Notice below that we can see A111 in the list but VLOOKUP is giving us an error. If you look closely at the value in cell A8 you will see that at the end there is a space whereas in A14 there is no space. In Excel’s world “A111” and “A111 ” (note the space at the end) are different.
- different formats of the data. If the lookup value is a number e.g. 1234 but in the table it is shown as 1234 but treated as text (left aligned), then Excel won’t find a match. Below note that the number 1100 in the table is left aligned and the cell is formatted as text. The lookup value in A14 is formatted as a number (right aligned). For Excel these are not the same thing hence the error.
- your table array is wrong. Most likely reasons are:
- your lookup value is not in the first column of your table array but in another column. As shown below, the formula is looking at the table from A4 to D8 but the lookup value of 1100 can only be found in the second column. Remember Excel only looks for the value in the first column it sees. It only uses the other columns to pull information back. In this case the table should be $B$4:$D$8
- You didn’t use dollar signs on the table array so as you copied it down, where Excel looked for the table array is incorrect. Notice below that because there are no dollar signs on the table, by the time it is copied down to cell B17, the table no longer contains the value we are looking for.
#N/A when you are using an Approximate match (TRUE or omitted as the last option)
This is much rarer as by definition an approximate match will approximate a number. The times you will get a #n/a with an Approximate match will be if:
- You lowest lookup number is lower than the lowest number in the table. As shown below our table starts at 10% but we are looking for 5% in the table.
- You didn’t use dollar signs on the table array so as you copied it down, where Excel looked for the table array is incorrect (same as the same issue for an exact match).