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.