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