IFNA

Returns a value if #N/A is found

Syntax:

IFNA(value, value_if_na)

IFNA tests a value and checks if #N/A was found. It returns value if #N/A was not found, otherwise returns value_if_na.

Example:

If A1 contains #N/A and A2 contains 1:

IFNA(A1, A2)

returns 1

If A1 contains 2 and A2 contains 1:

IFNA(A1, A2)

returns 2

Application:

Product Pricing Lookup

Imagine you have two tables:

1. Product List: This table contains all your products with their unique Product IDs and their corresponding prices.
2. Sales Order: This table lists items ordered by customers, using Product IDs, and you want to automatically pull the price for each ordered item from the "Product List."

The IFNA function has two main arguments:

1. value: This is the expression that IFNA will evaluate. If this expression results in an #N/A error, IFNA will then use its second argument.
2. value_if_na: This is the value that IFNA will return if the value argument evaluates to #N/A.

In our formula:

• value is INDEX(QUESTION3!C1:QUESTION3!C5,MATCH(B1,QUESTION3!A1:QUESTION3!A5,0))
• value_if_na is "Product Not Found"

Let's trace how this works for different scenarios:

Scenario 1: Product ID is Found (e.g., P001)

1. MATCH(B1,QUESTION3!A1:QUESTION3!A5,0):
2. • B1 contains "P001".
   • QUESTION3!C1:QUESTION3!C5 is the "Product ID" column in your "Product List" (Sheet1).
   • MATCH looks for "P001" in that range. It finds "P001" as the 1st item in that range.
   • So, MATCH returns 1.
2. INDEX(QUESTION3!C1:QUESTION3!C5, 1):
3. • QUESTION3!C1:QUESTION3!C5 is the "Price" column in your "Product List" (Sheet1).
   • INDEX then looks for the value in the 1st position of this "Price" column.
   • The value in the 1st position of QUESTION3!C1:QUESTION3!C5 is $1200.
   • So, the INDEX(MATCH(...)) part of the formula successfully returns $1200.
3. IFNA($1200, "Product Not Found"):
4. • IFNA receives $1200 as its value argument.
   • Since $1200 is not an #N/A error, IFNA simply returns the value itself.
   • Result: $1200 is displayed in cell D1.

Scenario 2: Product ID is NOT Found (e.g., P006)

1. MATCH(B3,QUESTION3!C1:QUESTION3!C5,0):
2. • B3 contains "P006".
   • MATCH looks for "P006" in QUESTION3!C1:QUESTION3!C5.
   • "P006" does not exist in that range.
   • When MATCH cannot find an exact match, it returns the #N/A error.
2. INDEX(QUESTION3!C1:QUESTION3!C5, #N/A):
3. • The INDEX function receives #N/A as its row number argument (from the MATCH function).
   • When an argument to a function is an error, the function typically propagates that error.
   • So, INDEX also returns the #N/A error.
3. IFNA(#N/A, "Product Not Found"):
4. • IFNA receives #N/A as its value argument.
   • Since the value is an #N/A error, IFNA then uses its value_if_na argument.
   • Result: "Product Not Found" is displayed in cell D3.

In Summary:

The IFNA function acts as an error trap specifically for #N/A errors. It wraps around the INDEX(MATCH(...)) combination.

• If INDEX(MATCH(...)) successfully finds a price, it returns that price, and IFNA simply passes that valid price through.
• If INDEX(MATCH(...)) fails to find a match (because MATCH returns #N/A), then INDEX also returns #N/A. IFNA then detects this specific #N/A error and replaces it with the friendly text "Product Not Found" instead of letting the raw #N/A error appear in the cell.