How to IFNA Function in Google Sheets

- Written by Puneet

excelchamps-free-courses-puneet-gogia

For example, you run an online store and keep track of your orders on Google Sheets. You have a list of product IDs and need to look up their names from another sheet.

Sometimes, a typo in the product ID or the product not being added yet can cause an error in your lookup formula.

The best use of IFNA is with the VLOOKUP. Even though you can use IFERROR, IFNA is a specific function that can be used with the VLOOKUP.

In this tutorial, we will learn about the IFNA in detail.

Intro to IFNA

IFNA function in Google Sheets helps you handle the #N/A error by showing a custom message instead of an error message when a formula returns it.

You can wrap your formula with IFNA and specify the value to display if an error occurs.

Syntax

Below is the syntax of the function:

=IFNA(value, value_if_na)
  • value: The formula or value you want to check for the #N/A error.
  • value_if_na: The value or message to return if the formula results in a #N/A error.

Example to understand it

As I said, the best use of IFNA is with the VLOOKUP, but with that, you can also use it with other LOOKUP functions. So, let’s understand it.

In the example below, when I look for ORDER ID 105, which is not in the data table, it returns #N/A as the result. To deal with this problem, you can use the IFNA function and wrap the main formula in it.

ifna-function-in-google-sheets
=IFNA(VLOOKUP(D3,A2:B6,1,0),"Product not found")
use-of-ifna-with-vlookup

When you use this formula and the lookup value isn’t found, IFNA returns the value you have defined instead of showing the #N/A error in the result.

Other Examples of Using IFNA with Other Functions

In the same way, here we have a few more examples of using IFNA.

INDEX and MATCH with IFNA

Like VLOOKUP, you can use IFNA with INDEX-MATCH to look for a value. This formula finds a value in column B corresponding to the value in cell A2. If it can’t find a match, it displays “Product not found”.

=IFNA(INDEX(B3:B6, MATCH(D3, A3:A6, 0)), "Product not found")
index-and-match-with-ifna

HLOOKUP with IFNA

You can also use IFNA with HLOOKUP. The formula looks up a value in the first row, 13, and returns the corresponding value from row 14. If no match is found, it displays “Product not found”.

hlookup-with-ifna

FILTER with IFNA

Using the IFNA with FILTER allows you to display a message when no data matches your criteria.

Let’s take an example to understand this. You have a list of products and their categories and want to filter products based on a specific category. If no products are in that category, you want to display a message instead of an error.

=IFNA(FILTER(A2:B6, B2:B6 = "Electronics"), "No products found in this category")
filter-with-ifna

FILTER looks for products in the specified category and returns them. If it finds “Electronics” products, it returns the data.

And if the FILTER returns an error if no product is in the “Electronics” category. By wrapping it with IFNA, you catch this error and instead display “No products found in this category”.

IFNA Vs. IFERROR

IFERROR is the closest function to IFNA, with two major differences. Before using any of these functions, you can consider these differences.

  • IFNA handles only #N/A errors, while IFERROR handles all errors.
  • IFNA is best for lookup errors like those from VLOOKUP, while IFERROR is ideal for using any function where you can get an error in the result.
Last Updated: June 24, 2024