How to identify "#N/A" in a formula in Excel 2003?

Question

Sometimes a formula in Excel 2003 returns #N/A (like VLOOKUP formula) and I want to know how to identify this "#N/A". For example, I want to hide #N/A like this: =if(VLOOKUP(**;;*)=#N/A;value_if_true;value_if_false). But this will return invariable #N/A.

Thanks for help.

Answer 1

N/A comes when the data range you have used has incorrect or invalid input. Try the IF range or COUNTIF range for pulling up the data ranges.

Answer 2

Excel returns these errors when an element inside a formula is wrong, missing or not available. For a formula to work properly and to display the correct results, all elements from the formula must have correct values. On the other hand, #N/A errors are encountered when Excel cannot find a match for the value it's been told to find and it displays that message.

In order to avoid this error please use IF or COUNTIF instead of VLOOKUP and you will see the results will improve.