Usually, the #N/A error appears when you deal with lookup functions, such as VLOOKUP, HLOOKUP, LOOKUP, and MATCH.
If you encounter this error, it means that the formula can’t find a referenced value.
Take a look at the following Example. The table below has a list of people who we want to check. In the table above, (B2, C2, D2, E2) we have 4 VLOOKUP tables. Each of these formulas looks at cell A2 and searches for the relevant data.
|
1 2 3 4 |
B2: =VLOOKUP(A2,A7:E11,2,FALSE) C2: =VLOOKUP(A2,A7:E11,3,FALSE) D2: =VLOOKUP(A2,A7:E11,4,FALSE) E2: =VLOOKUP(A2,A7:E11,5,FALSE) |
You can get this type of error if there is no “Ava” name inside the column “First Name”, in the second table or the cell A2 is blank.
In order to remove this error, you have to check whether there is the #N/A error and return something else instead, let’s say a dash.
Here is the code.
|
1 |
=IF(ISNA(VLOOKUP(A2,A7:E11,2,FALSE)),"-",VLOOKUP(A2,A7:E11,2,FALSE)) |
Code explanation
Excel checks whether the formula returns the #N/A error. If it does, the #N/A is replaced with “-“, otherwise the value from VLOOKUP is placed instead.
