Many functions return ‘#N/A’ which can corrupt an other wise clean set of calculations.

For example, suppose you’re performing a VLOOKUP on a two column list of products and prices and you’re looking for a product not listed. Your VLOOKUP formula will return a ‘#N/A’ result.

If this formula is part of a larger calculation – the total cost of a multiple set of purchases for example – it would infect the whole calculation which would now also return ‘#N/A’. Even worse, any cell or formula anywhere in your spreadsheet (eg a complex quote for a customer) that has this result as an input will return ‘#N/A’. This can be very annoying if a large spreadsheet is compromised due to just one missing product name.

Anyway, here are some of the ways to deal with this ‘dreaded #N/A’ error…

### Add The Missing Data

This is the simplest method: just add the missing data (eg the missing product in our example)

Indeed remaining any ‘#N/A’ errors from a calculation is a good control over data completeness

### ISNA()

The ISNA function returns TRUE if a cell contains #N/A and FALSE if not.

It can be used in other formulas (eg IF formulas) to deal with any #N/A arising from a calculation.

Here’s more on this function:

https://exceljet.net/functions/isna-function

### IFNA()

A combination of the IF function and ISNA, this function allows you to specify what you want your spreadsheet to do if it encounters an #N/A (or any other error).

For you might want to display an error message if there’s an #N/A, or return zero so that the calculation can proceed.

Here’s more on this function: