I. Intro
The IFNA function returns a value that you specify when a formula returns an #N/A error (data missing). Otherwise, it returns the result of the formula.
The IFNA function is often used the same way as IFERROR. You can think of it as a more specific IFERROR function, where IFNA only checks for #N/A errors in formulas. Click here to learn more about the IFERROR function.
II. About the function
- Formula: =IFNA(value, #N/A response value)
- Parameters:
- Value: Used for checking #N/A error values (conditions).
- #N/A response value: The response value when a formula contains an #N/A error.
- Example:
- IFNA is often used with VLOOKUP
- =IFNA(VLOOKUP("New York",A5:B10,3,0),"Not Found")
- This formula uses VLOOKUP to look for a specific value, and if no such value exists, "Not Found" is returned.
- Note:
- Use double quotation marks (") in the formula.
III. Steps
Use the IFNA function
- Select a cell and click Formula in the toolbar, then select Logical > IFNA. You can also directly enter =IFNA in a cell.
- Enter the parameters in the cell. =IFNA(VLOOKUP(F2,A1:D6,4,0),"no score") is used in this example.
- Press Enter to display the result.
250px|700px|reset
Delete the IFNA function
Select the cell with the IFNA function and press Delete.
IV. Use cases
Auditing
IFNA and VLOOKUP are handy for auditing when specific employee data, such as sales figures, is needed for analysis. To learn more about the VLOOKUP function, see What is the VLOOKUP function?
- Formula used below: =IFNA(VLOOKUP(E2,A1:C11,3,0), "Can't find")
- About the parameters: The GIF below shows the use of VLOOKUP in combination with IFNA.
- The VLOOKUP function, set to look up employee sales, is used as the value parameter for the IFNA function.
- E2 is the name of the person to look up.
- A1:C11 is the range to look up. Note that this includes the headings.
- 3 refers to the third column in the range that lists the sales figures.
- 0 is fixed, representing an exact match. "Can't find" is returned if the conditional value is not found.
250px|700px|reset