replace NA with another message in Excel
Q81. My VLOOKUP formula returns #N/A when no match is found. Instead of #N/A, can I make it display a more meaningful text like "NO MATCH FOUND"?
For example, in the Table below, I get "#N/A" result in 2 of the fields since the Student name entered does not match any of the student names in the Table. Instead of "#N/A", can I make it display "RESULTS NOT AVAILABLE"?
To do it in Excel, here is the answer:
a) Update the formula to =IFERROR(VLOOKUP(I3,D3:F22,3,FALSE),"RESULTS NOT AVAILABLE") as shown below.
Basically this formula wraps the IFERROR around VLOOKUP function. When an error is returned by VLOOKUP, "RESULTS NOT AVAILABLE" is displayed.
IFEEROR could be wrapped around all functions where errors have to be handled in a certain way.
b) Cell I5 (screenshot below) having formula =IFERROR(INDEX(C3:C22,MATCH(I3,D3:D22,0)),"RESULTS NOT AVAILABLE") is an example of wrapping IFERROR around INDEX / MATCH functions.