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"?

excel replace na with another message

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.

excel excel replace na with another message

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.

excel excel excel replace na with another message

 

You can find similar Excel Questions and Answer hereunder

1) How can I plot 2 series on the same chart with different scales / measurement unit for Values (Ex: Pareto chart)?

2) How to concatenate strings in vba in Excel

3) I want to add a trend indicator symbol next to my sales data - how can I do that?

4) How do I enter a formula in a cell using VBA (using Absolute Reference)?

5) How can I add a Timestamp after macro execution?

6) I have 2 sets of lists from 2 different reports - how can I select the cells with differences?

7) Applying a countif formula only to visible cells in a filtered list in Excel

8) How do I update my DropDown list whenever the sheet is activated?

9) I conducted a test and the test scores are available - how can I get Percentile rank of students?

10) Line break in vba message box in Excel

11) How to add a link in a sheet to another sheet

12) How can I find time interval between 2 times?

13) How do I disable the right click option for users in my WorkBook?

14) How can I have text autocomplete based on values previously entered in Column?

15) Calling a macro from another workbook in Excel