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

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.

``=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.

``=IFERROR(F3,"Value Not Found")`` Combining the functions together gives us our original formula:

``=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:

``=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!).

``=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

``=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:

``=VLOOKUP(E3,B3:C7,2,FALSE)`` This will perform the lookup on the second sheet:

``=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.

``=IFNA(F3,G3)`` Combining all the functions gives our original formula:

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

### Easy Excel Automation ## VLOOKUP with IFNA/IFERROR in Google Sheets

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

``=IFNA(VLOOKUP(E3,B3:C7,2,FALSE),"Value not Found")`` ``=IFERROR(VLOOKUP(E3,B3:C7,2,FALSE),"Value not Found")`` ### Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!