Replace na with another message in Excel

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 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 replace na with another message


You can find similar Excel Questions and Answer hereunder

1) How to display messages boxes in VBA with the msgbox function

2) How to use goal seek in Excel. Some good introduction to goal seeking function

3) Here some explanations about the XML DOM node in VBA, DOM stands for Document Object Model

4) Vba clear the contents of an entire sheet in Excel

5) Line break in vba message box in Excel

6) How can I display list of NAMES in a worksheet?

7) How can I execute an event at a predetermined time from my Macro?

8) I have data arranged in rows of information. Is there any way I can enter new data through a form instead of entering it directly in SpreadSheet?

9) How can I find the number of working days between 2 dates given a holidays list?

10) What is a function in VBA. How to create a function and how to call a function


Here the previous and next chapter