Скачать или смотреть Google Sheets | IFNA Function | Trap

In Google Sheets, trap the #N/A error, using the IFNA function and, for example, display a custom message. Nest the function that produces the #N/A error inside the IFNA function to trap this error. This tutorial demos the steps to catch this error by nesting MATCH inside IFNA.

Whereas the IFNA function has one mandatory and one optional argument, the MATCH function has two mandatory and one optional arguments.

-------------------------------------
Google Sheets Quiz with Demo of Answers

Try this 5-question quiz, which also demos the answers. The quiz
can be helpful in your day-to-day spreadsheet work or for job interview:

   • Google Sheets Functions Quiz | Fill-in-the...  

IFNA Function

Here is the link to the step-by-step tutorial for using the IFNA function for
displaying a custom message with the #N/A error:

   • Google Sheets | IFNA Function | Display Me...  

MATCH Function

If you wish to find the position of an item in a list, the MATCH function can
be helpful. Here is the link to the step-by-step tutorial on MATCH:

   • Google Sheets Match | Match Function Googl...  
-------------------------------------

Here is the format of the IFNA function formula:

=IFNA(value, [value_if_na_error])

Start the formula with an equal-to symbol.

IFNA is the name of the function.

value can be a cell address, range address, or function, and this value will be returned if the cell doesn't have the #N/A error.

value_if_na_error is optional, and is the value that the function returns in case of #N/A error. If this argument is omitted, the IFNA function does not display any message.

Let's look at an example of the IFNA function formula:

=IFNA(B33, "No drawing num.")

The above formula will return the value in the cell B33, if the formula in it
has not produced the #N/A error. Otherwise, it will return the message "No drawing number".

Let's look at the format of the IFNA function formula:

=MATCH(search_key, range, [search_type])

Start the formula with an equal-to symbol.

MATCH is the name of the function.

search_key is the data to search.

range is the area of the spreadsheet to search for the search_key.

search_type can have one of the three values: 0, 1, or -1. The value 0 specifies exact search, if the data is not sorted. The values 1 and -1 specifies approximate search, if the data is sorted in ascending and descending order respectively.

The MATCH function produces the #N/A error, if it does not find the search_key in the specified range. Trap this error and, say, display a custom message, by nesting MATCH inside IFNA.

Example

Say you want to search for a drawing number in a list of drawing numbers, and if the drawing number is not found, trap the #N/A error that MATCH produces and display a custom message like "No drawing". A way to accomplish this task is to use the IFNA and MATCH functions.

Here is the format of the IFNA-MATCH functions combo formula:

=IFNA(MATCH(search_key, range, [search_type]), [value_if_na_error])

=ifna(match("14561-VA19",A31:A40,0),"No drawing")

If the drawing number 14561-VA19 is found in the range A31 to A40, MATCH returns its position in the list. Otherwise, it will return the message No drawing.

Review this tutorial, which gives the steps to use the Google Sheets IFNA function to trap the #N/A error. As an example, this tutorial nests the MATCH function inside IFNA to trap this error and display a custom message.