# VLOOKUP – Fix #N/A Error – Excel & Google Sheets

Download the example workbook

*This tutorial will demonstrate how to handle #N/A Errors when performing VLOOKUPs in Excel and Google Sheets. If your version of Excel supports XLOOKUP, we recommend using the XLOOKUP Function instead.*

**VLOOKUP: #N/A Error-Handling**

The VLOOKUP Function returns the #N/A Error when it fails to find a match. Instead, you may want to return some other value if a match is not found. There are two functions that we can use to replace this error with a customized value: IFNA and IFERROR.

**VLOOKUP with IFNA**

In this example, we will replace the #N/A Error with the text “Value Not Found” using the IFNA Function.

1 |
=IFNA(VLOOKUP(E3,B3:C7,2,FALSE),"Value Not Found") |

Let’s walk through the formula above:

**VLOOKUP Function**

The VLOOKUP Function searches for the lookup value (e.g., Sub), and if it doesn’t find a match, it will return the #N/A Error.

Note: The VLOOKUP Function searches for the lookup value from the first column of the table and returns the corresponding value from the column defined by the col_index_num.

**IFNA Function**

The IFNA Function looks for the #N/A Error. If found, the specified value (e.g., “Value Not Found”) is returned.

1 |
=IFERROR(F3,"Value Not Found") |

Combining the functions together gives us our original formula:

1 |
=IFNA(VLOOKUP(E3,B3:C7,2,FALSE),"Value Not Found") |

Instead of a text, we can also replace the #N/A Error with a numerical value like 0:

1 |
=IFNA(VLOOKUP(E3,B3:C7,2,FALSE),0) |

**VLOOKUP with IFERROR**

The IFNA and IFERROR functions work exactly the same way, except the IFERROR Function handles all types of errors (e.g., #N/A, #REF!, #VALUE!).

1 |
=IFERROR(VLOOKUP(E3,B3:C7,2,FALSE),"Value Not Found") |

We recommend using the IFNA function instead of the more common IFERROR because IFNA will only replace the #N/A error. Using IFNA will make sure you don’t miss other important errors.

**VLOOKUP: Multiple Sheets at Once**

Instead of a customized value, we can nest another VLOOKUP Function inside IFNA/IFERROR if we want to perform a VLOOKUP on multiple sheets at once.

**VLOOKUP with IFNA: Two Sheets at Once**

1 |
=IFNA(VLOOKUP(E3,B3:C7,2,FALSE),VLOOKUP(E3,'2 Sheets - Data 2'!B3:C7,2,FALSE)) |

Let’s walk through the formula above:

**VLOOKUP Function**

We need to perform a VLOOKUP on each sheet. This will perform the lookup on the first sheet:

1 |
=VLOOKUP(E3,B3:C7,2,FALSE) |

This will perform the lookup on the second sheet:

1 |
=VLOOKUP(E3,'2 Sheets - Data 2'!B3:C7,2,FALSE) |

**IFNA Function**

Then we use the IFNA Function to return the result of the second VLOOKUP, if the first VLOOKUP does not find a match.

1 |
=IFNA(F3,G3) |

Combining all the functions gives our original formula:

1 |
=IFNA(VLOOKUP(E3,B3:C7,2,FALSE),VLOOKUP(E3,'2 Sheets - Data 2'!B3:C7,2,FALSE)) |

**VLOOKUP with IFNA/IFERROR in Google Sheets**

The IFNA-VLOOKUP and IFERROR-VLOOKUP work the same way in Google Sheets as in Excel.

1 |
=IFNA(VLOOKUP(E3,B3:C7,2,FALSE),"Value not Found") |

1 |
=IFERROR(VLOOKUP(E3,B3:C7,2,FALSE),"Value not Found") |