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. In the table below, it 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 this formula looks at the cell A2 and searches for the relevant data.
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 check whether there is the #N/A error and return something else instead, let’s say a dash.
Here is the code.
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.