Excel IFERROR, ISERROR, ISERR, IFNA and ISNA Functions Tutorial: Step-by-Step Guide and 14 Examples to Handle Errors and Use IS Functions in Formulas

In this Excel IFERROR, ISERROR, ISERR, IFNA and ISNA Tutorial, you learn how to use the IFERROR, ISERROR, ISERR, IFNA and ISNA functions in your worksheet formulas for the following:

• Identify errors, including the #N/A error.
• Handle errors, including the #N/A error, and return a specific:
  • Value;
  • Formula;
  • Expression; or
  • Reference.
• Carry out VLookups that handle errors, including the #N/A error, and return a specific:
  • Value;
  • Formula;
  • Expression; or
  • Reference.
• Check whether a specific value exists in a list or compare 2 columns.

This Excel IFERROR, ISERROR, ISERR, IFNA and ISNA Tutorial is accompanied by an Excel workbook containing the data and formulas I use in the examples below. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.

Use the following Table of Contents to navigate to the section you're interested in.

Related Excel Tutorials

The following Tutorials may help you better understand and implement the contents below:

• Formulas and functions:
  • Learn how to work with the LEFT, RIGHT, MID, LEN, FIND and SEARCH functions here.
• Macros and VBA:
  • Learn how to use worksheet functions in macros here.
  • Learn how to work with the VLookup function in VBA here.

You can find additional Tutorials in the Archives.

#1: IFERROR

IFERROR formula

To handle possible errors with the IFERROR function, use a formula with the following structure:

=IFERROR(Value,ValueIfError)

IFERROR process

To handle possible errors with the IFERROR function, follow these steps:

1. Specify the expression you want to check for errors (Value).
2. Specify that, if Value returns an error (IFERROR), another value (ValueIfError) is returned.

IFERROR formula explanation

Item: IFERROR

The IFERROR function:

• Returns the value you specify (ValueIfError) if an expression (Value) returns an error; and
• Returns the result of that expression (Value) otherwise.

In other words, IFERROR does the following:

1. Checks an expression (Value).
2. If Value returns an error, IFERROR returns the value you specify (ValueIfError).
3. If Value doesn't return an error, IFERROR returns the result of that expression.

Therefore, you usually use IFERROR to trap and handle errors in worksheet formulas. The IFERROR function deals with the following errors:

• #N/A.
• #VALUE!
• #REF!
• #DIV/0!
• #NUM!
• #NAME?
• #NULL!

Item: Value

The value argument of the IFERROR function (Value) is a value, formula, expression or reference that Excel checks for errors.

If Value doesn't return an error, IFERROR returns the result of that expression.

Item: ValueIfError

The value_if_error argument of the IFERROR function (ValueIfError) is the value, formula, expression or reference that Excel returns if the value argument of the IFERROR function (Value) evaluates to an error.

IFERROR formula example

The worksheet formulas below handle possible errors with the IFERROR function, as follows:

• Value: The quotient obtained by dividing:
  • The value specified in column G (G12 to G16); by
  • The value specified in column F (F12 to F16).
• ValueIfError: The string “Total Sales are $ 0” (“Total Sales are $ 0”).

No.	IFERROR formula
1	=IFERROR(G12/F12,"Total Sales are $ 0")
2	=IFERROR(G13/F13,"Total Sales are $ 0")
3	=IFERROR(G14/F14,"Total Sales are $ 0")
4	=IFERROR(G15/F15,"Total Sales are $ 0")
5	=IFERROR(G16/F16,"Total Sales are $ 0")

Effects of using IFERROR formula example

The following image illustrates the results returned by the IFERROR formula that handles possible errors. As expected, the formulas (in cells H12 to H16):

1. Check an expression (Value) for errors; and
2. Return the following:
   1. If Value returns an error: The string “Total Sales are $ 0” (ValueIfError).
   2. If Value doesn't return an error: Value itself.

Notice the difference between the result returned by the IFERROR formula that handles errors in cell H16 and the result returned by the regular formula (without IFERROR) in cell H11.

#2: IFERROR then 0

IFERROR then 0 formula

To return 0 if an expression returns an error (with the IFERROR function), use a formula with the following structure:

=IFERROR(Value,0)

IFERROR then 0 process

To return 0 if an expression returns an error (with the IFERROR function), follow these steps:

1. Specify the expression you want to check for errors (Value).
2. Specify that, if Value returns an error (IFERROR), 0 (0) is returned.

IFERROR then 0 formula explanation

Item: IFERROR

The IFERROR function:

• Returns the value you specify (0) if an expression (Value) returns an error; and
• Returns the result of that expression (Value) otherwise.

In other words, IFERROR does the following:

1. Checks an expression (Value).
2. If Value returns an error, IFERROR returns the value you specify (0).
3. If Value doesn't return an error, IFERROR returns the result of that expression.

Therefore, you usually use IFERROR to trap and handle errors in worksheet formulas. The IFERROR function deals with the following errors:

• #N/A.
• #VALUE!
• #REF!
• #DIV/0!
• #NUM!
• #NAME?
• #NULL!

Item: Value

The value argument of the IFERROR function (Value) is a value, formula, expression or reference that Excel checks for errors.

If Value doesn't return an error, IFERROR returns the result of that expression.

Item: 0

The value_if_error argument of the IFERROR function (0) is the value, formula, expression or reference that Excel returns if the value argument of the IFERROR function (Value) evaluates to an error.

To return 0 if an expression (Value) returns an error, set value_if_error to 0.

IFERROR then 0 formula example

The worksheet formulas below return 0 if an expression returns an error (with the IFERROR function), where Value is the quotient obtained by dividing:

• The value specified in column G (G22 to G26); by
• The value specified in column F (F22 to F26).

No.	IFERROR then 0 formula
1	=IFERROR(G22/F22,0)
2	=IFERROR(G23/F23,0)
3	=IFERROR(G24/F24,0)
4	=IFERROR(G25/F25,0)
5	=IFERROR(G26/F26,0)

Effects of using IFERROR then 0 formula example

The following image illustrates the results returned by the IFERROR formula that handles possible errors by returning 0. As expected, the formulas (in cells H22 to H26):

1. Check an expression (Value) for errors; and
2. Return the following:
   1. If Value returns an error: 0.
   2. If Value doesn't return an error: Value itself.

Notice the difference between the result returned by the IFERROR formula that handles errors by returning 0 in cell H26 and the result returned by the regular formula (without IFERROR then 0) in cell H21.

#3: IFERROR then blank

IFERROR then blank formula

To return a blank if an expression returns an error (with the IFERROR function), use a formula with the following structure:

=IFERROR(Value,"")

IFERROR then blank process

To return a blank if an expression returns an error (with the IFERROR function), follow these steps:

1. Specify the expression you want to check for errors (Value).
2. Specify that, if Value returns an error (IFERROR), a zero-length string (“”) is returned.

IFERROR then blank formula explanation

Item: IFERROR

The IFERROR function:

• Returns the value you specify (“”) if an expression (Value) returns an error; and
• Returns the result of that expression (Value) otherwise.

In other words, IFERROR does the following:

1. Checks an expression (Value).
2. If Value returns an error, IFERROR returns the value you specify (“”).
3. If Value doesn't return an error, IFERROR returns the result of that expression.

Therefore, you usually use IFERROR to trap and handle errors in worksheet formulas. The IFERROR function deals with the following errors:

• #N/A.
• #VALUE!
• #REF!
• #DIV/0!
• #NUM!
• #NAME?
• #NULL!

Item: Value

The value argument of the IFERROR function (Value) is a value, formula, expression or reference that Excel checks for errors.

If Value doesn't return an error, IFERROR returns the result of that expression.

Item: “”

The value_if_error argument of the IFERROR function (“”) is the value, formula, expression or reference that Excel returns if the value argument of the IFERROR function (Value) evaluates to an error.

To return a blank if an expression (Value) returns an error, set value_if_error to a zero-length string (“”).

IFERROR then blank formula example

The worksheet formulas below return a blank if an expression returns an error (with the IFERROR function), where Value is the quotient obtained by dividing:

• The value specified in column G (G32 to G36); by
• The value specified in column F (F32 to F36).

No.	IFERROR then blank formula
1	=IFERROR(G32/F32,"")
2	=IFERROR(G33/F33,"")
3	=IFERROR(G34/F34,"")
4	=IFERROR(G35/F35,"")
5	=IFERROR(G36/F36,"")

Effects of using IFERROR then blank formula example

The following image illustrates the results returned by the IFERROR formula that handles possible errors by returning a blank (“”). As expected, the formulas (in cells H32 to H36):

1. Check an expression (Value) for errors; and
2. Return the following:
   1. If Value returns an error: A zero-length string (“”).
   2. If Value doesn't return an error: Value itself.

Notice the difference between the result returned by the IFERROR formula that handles errors by returning a blank in cell H36 and the result returned by the regular formula (without IFERROR then blank) in cell H31.

#4: IFERROR VLOOKUP

IFERROR VLOOKUP formula

To carry out a VLookup that handles possible errors (with IFERROR vs. IFNA), use a formula with the following structure:

=IFERROR(VLOOKUP(LookupValue,LookupTable,ColumnIndex,RangeLookup),ValueIfError)

IFERROR VLOOKUP process

To carry out a VLookup that handles possible errors (with IFERROR vs. IFNA), follow these steps:

1. Specify the value you want to look up (LookupValue) in the first (leftmost) column of a table (LookupTable).
2. Identify the cell range (a table array) containing the lookup table (LookupTable).
3. Specify the index number of the column (within LookupTable) from which you want to obtain a value (ColumnIndex).
4. Specify whether VLOOKUP searches for an exact or approximate match (RangeLookup).
5. Specify that, if VLOOKUP returns an error (IFERROR), another value (ValueIfError) is returned.

IFERROR VLOOKUP formula explanation

Formula #1: VLOOKUP(LookupValue,LookupTable,ColumnIndex,RangeLookup)

Item: VLOOKUP

The VLOOKUP function does the following:

1. Looks for a value (LookupValue) in the first (leftmost) column of a table (LookupTable); and
2. Returns a value in the same row but from another column you specify (ColumnIndex).

Item: LookupValue

The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of LookupTable. In other words, LookupValue must usually be in the first column of the cell range you specify as LookupTable.

If VLOOKUP doesn't find LookupValue in the first column of LookupTable, it usually returns the #N/A error.

You can specify LookupValue as either:

• A value;
• A text string; or
• A cell reference.

Item: LookupTable

The table_array argument of the VLOOKUP function (LookupTable) is the cell range in which VLOOKUP searches for the following:

1. The LookupValue in the first column of LookupTable; and
2. The value to return in the column you specify (ColumnIndex).

Therefore, the cell range you specify as LookupTable must usually include both of the following columns:

• The first column, which must contain the LookupValue; and
• The column from which VLOOKUP should return a value.

If VLOOKUP doesn't find LookupValue in the first column of LookupTable, it usually returns the #N/A error.

Item: ColumnIndex

The col_index_num argument of the VLOOKUP function (ColumnIndex) is the column number within the LookupTable from which VLOOKUP returns a value, as follows:

Column	ColumnIndex	Comments
First	1	Must usually contain the LookupValue. Otherwise, VLOOKUP usually returns the #N/A error.
Second	2
Third	3
…	…
#th	#

Item: RangeLookup

The range_lookup argument of the VLOOKUP function (RangeLookup) specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of LookupTable.

• Set RangeLookup to TRUE when searching for an approximate match.
• Set RangeLookup to FALSE when searching for an exact match.

Formula #2: IFERROR(VLOOKUP(…),ValueIfError)

Item: IFERROR

The IFERROR function:

• Returns the value you specify (ValueIfError) if an expression (VLOOKUP(…)) returns an error; and
• Returns the result of that expression (VLOOKUP(…)) otherwise.

In other words, IFERROR does the following:

1. Checks an expression (VLOOKUP(…)).
2. If VLOOKUP(…) returns an error, IFERROR returns the value you specify (ValueIfError).
3. If VLOOKUP(…) doesn't return an error, IFERROR returns the result of that formula.

Item: VLOOKUP(…)

The value argument of the IFERROR function (VLOOKUP(…)) is a value, formula, expression or reference that Excel checks for errors.

If VLOOKUP(….) doesn't return an error, IFERROR returns the result of that formula. For the explanation of this VLOOKUP function, please refer to the appropriate section in this Tutorial.

One of the most common errors returned by the VLOOKUP function is #N/A. The VLOOKUP function usually returns an #N/A error when you either:

1. Give an inappropriate value (including a value that isn't found in the first column of LookupTable) for the lookup_value argument (LookupValue); or
2. Use the function to locate a value in a table (LookupTable) that isn't properly sorted.

Item: ValueIfError

The value_if_error argument of the IFERROR function (ValueIfError) is the value, formula, expression or reference that Excel returns if the value argument of the IFERROR function (VLOOKUP(…)) evaluates to an error.

IFERROR VLOOKUP formula example

The worksheet formula below carries out an exact match VLookup and handles possible errors (with IFERROR vs. IFNA), as follows:

• LookupValue: The value specified in cell M8 ($M$8).
• LookupTable: The lookup table in cells A8 to E57 ($A$8:$E$57).
• ColumnIndex: The column number specified in column K (K10).
• RangeLookup: FALSE.
• ValueIfError: The string “Sales Manager not found” (“Sales Manager not found”).

=IFERROR(VLOOKUP($M$8,$A$8:$E$57,K10,FALSE),"Sales Manager not found")

Effects of using IFERROR VLOOKUP formula example

The following images illustrate the results returned by the IFERROR VLOOKUP formula that carries out a VLookup that handles possible errors (with IFERROR vs. IFNA).

The image below displays the LookupTable.

The image below displays the results returned by IFERROR VLOOKUP. As expected, the formula in cell M10 does the following:

1. Looks for the value (LookupValue) specified in cell M8 (Shawn Brooks) in the first column (column A) of the lookup table (LookupTable).
2. The LookupValue (Shawn Brooks) isn't found in the first column of LookupTable. Therefore, the IFERROR VLOOKUP formula returns “Sales Manager not found”.

Notice the difference between the result returned by the IFERROR VLOOKUP formula that handles errors (cell M10) and the result returned by the regular VLOOKUP formula in cell M9 (#N/A).

#5: ISERROR

ISERROR formula

To check whether an expression returns an error (with the ISERROR function), use a formula with the following structure:

=ISERROR(Value)

ISERROR process

To check whether an expression returns an error (with the ISERROR function), specify the expression you want to check for errors (Value).

ISERROR formula explanation

Item: ISERROR

The ISERROR function:

• Tests whether an expression (Value) returns an error; and
• Returns:
  • TRUE if Value returns an error; or
  • FALSE otherwise.

The ISERROR function identifies the following errors:

• #N/A.
• #VALUE!
• #REF!
• #DIV/0!
• #NUM!
• #NAME?
• #NULL!

Item: Value

The value argument of the ISERROR function (Value) is a value, formula, expression or reference that Excel checks for errors.

ISERROR formula example

The worksheet formulas below check whether an expression returns an error (with the ISERROR function), where Value is the quotient obtained by dividing:

• The value specified in column G (G42 to G46); by
• The value specified in column F (F42 to F46).

No.	ISERROR formula
1	=ISERROR(G42/F42)
2	=ISERROR(G43/F43)
3	=ISERROR(G44/F44)
4	=ISERROR(G45/F45)
5	=ISERROR(G46/F46)

Effects of using ISERROR formula example

The following image illustrates the results returned by the ISERROR formula that checks whether an expression returns an error. As expected, the formulas (in cells H42 to H46):

1. Check an expression (Value) for errors; and
2. Return the following:
   1. TRUE: If Value returns an error.
   2. FALSE: If Value doesn't return an error.

Notice the difference between the result returned by the ISERROR formula that checks whether an expression returns an error in cell H46 and the result returned by the regular formula (without ISERROR) in cell H41.

#6: IF ISERROR

IF ISERROR formula

To handle possible errors (with IF ISERROR vs. IFERROR), use a formula with the following structure:

=IF(ISERROR(Value),ValueIfError,Value)

IF ISERROR process

To handle possible errors (with IF ISERROR vs. IFERROR), follow these steps:

1. Specify the expression you want to check for errors (Value).
2. Test whether Value returns an error (ISERROR) and specify (IF) that:
   1. If Value returns an error, another value (ValueIfError) is returned; and
   2. If Value doesn't return an error, Value itself is returned.

IF ISERROR formula explanation

Formula #1: ISERROR(Value)

Item: ISERROR

The ISERROR function:

• Tests whether an expression (Value) returns an error; and
• Returns:
  • TRUE if Value returns an error; or
  • FALSE otherwise.

The ISERROR function identifies the following errors:

• #N/A.
• #VALUE!
• #REF!
• #DIV/0!
• #NUM!
• #NAME?
• #NULL!

Item: Value

The value argument of the ISERROR function (Value) is a value, formula, expression or reference that Excel checks for errors.

If Value doesn't return an error, IF returns the result of that expression. For the explanation of this IF function, please refer to the appropriate section in this Tutorial.

Formula #2: IF(ISERROR(…),ValueIfError,Value)

Item: IF

The IF function:

• Tests whether a condition (ISERROR(…)) is met; and
• Returns:
  • One value (ValueIfError) if the condition (ISERROR(…)) is met and returns TRUE; and
  • Another value (Value) if the condition (ISERROR(…)) isn't met and returns FALSE.

Item: ISERROR(…)

The logical_test argument of the IF function (ISERROR(…)) is the condition Excel tests and evaluates to either:

• TRUE; or
• FALSE.

For the explanation of this ISERROR function, please refer to the appropriate section in this Tutorial.

Item: ValueIfError

The value_if_true argument of the IF function (ValueIfError) is the value, formula, expression or reference that Excel returns if ISERROR(…) returns TRUE.

In other words, ValueIfError is the value, formula, expression or reference that Excel returns if the value argument of the ISERROR function (Value) returns an error.

Item: Value

The value_if_false argument of the IF function (Value) is the value, formula, expression or reference that Excel returns if ISERROR(…) returns FALSE.

In other words, Value is the value, formula, expression or reference that Excel returns if the value argument of the ISERROR function (Value itself) doesn't return an error.

IF ISERROR formula example

The worksheet formulas below handle possible errors (with IF ISERROR vs. IFERROR), as follows:

• Value: The quotient obtained by dividing:
  • The value specified in column G (G52 to G56); by
  • The value specified in column F (F52 to F56).
• ValueIfError: The string “Total Sales are $ 0” (“Total Sales are $ 0”).

No.	IF ISERROR formula
1	=IF(ISERROR(G52/F52),"Total Sales are $ 0",G52/F52)
2	=IF(ISERROR(G53/F53),"Total Sales are $ 0",G53/F53)
3	=IF(ISERROR(G54/F54),"Total Sales are $ 0",G54/F54)
4	=IF(ISERROR(G55/F55),"Total Sales are $ 0",G55/F55)
5	=IF(ISERROR(G56/F56),"Total Sales are $ 0",G56/F56)

Effects of using IF ISERROR formula example

The following image illustrates the results returned by the IF ISERROR formula that handles possible errors (with IF ISERROR vs. IFERROR). As expected, the formulas (in cells H52 to H56):

1. Check an expression (Value) for errors; and
2. Return the following:
   1. If Value returns an error: The string “Total Sales are $ 0” (ValueIfError).
   2. If Value doesn't return an error: Value itself.

Notice the difference between the result returned by the IF ISERROR formula that handles errors in cell H56 and the result returned by the regular formula (without IF ISERROR) in cell H51.

#7: IF ISERROR VLOOKUP

IF ISERROR VLOOKUP formula

To carry out a VLookup that handles possible errors (with IF ISERROR vs. IFERROR), use a formula with the following structure:

=IF(ISERROR(VLOOKUP(LookupValue,LookupTable,ColumnIndex,RangeLookup)),ValueIfError,VLOOKUP(LookupValue,LookupTable,ColumnIndex,RangeLookup))

IF ISERROR VLOOKUP process

To carry out a VLookup that handles possible errors (with IF ISERROR vs. IFERROR), follow these steps:

1. Specify the value you want to look up (LookupValue) in the first (leftmost) column of a table (LookupTable).
2. Identify the cell range (a table array) containing the lookup table (LookupTable).
3. Specify the index number of the column (within LookupTable) from which you want to obtain a value (ColumnIndex).
4. Specify whether VLOOKUP searches for an exact or approximate match (RangeLookup).
5. Test whether VLOOKUP returns an error (ISERROR) and specify (IF) that:
   1. If VLOOKUP returns an error, another value (ValueIfError) is returned; and
   2. If VLOOKUP doesn't return an error, the result of VLOOKUP is returned.

IF ISERROR VLOOKUP formula explanation

Formula #1: VLOOKUP(LookupValue,LookupTable,ColumnIndex,RangeLookup)

Item: VLOOKUP

The VLOOKUP function does the following:

1. Looks for a value (LookupValue) in the first (leftmost) column of a table (LookupTable); and
2. Returns a value in the same row but from another column you specify (ColumnIndex).

Item: LookupValue

The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of LookupTable. In other words, LookupValue must usually be in the first column of the cell range you specify as LookupTable.

If VLOOKUP doesn't find LookupValue in the first column of LookupTable, it usually returns the #N/A error.

You can specify LookupValue as either:

• A value;
• A text string; or
• A cell reference.

Item: LookupTable

The table_array argument of the VLOOKUP function (LookupTable) is the cell range in which VLOOKUP searches for the following:

1. The LookupValue in the first column of LookupTable; and
2. The value to return in the column you specify (ColumnIndex).

Therefore, the cell range you specify as LookupTable must usually include both of the following columns:

• The first column, which must contain the LookupValue; and
• The column from which VLOOKUP should return a value.

If VLOOKUP doesn't find LookupValue in the first column of LookupTable, it usually returns the #N/A error.

Item: ColumnIndex

The col_index_num argument of the VLOOKUP function (ColumnIndex) is the column number within the LookupTable from which VLOOKUP returns a value, as follows:

Column	ColumnIndex	Comments
First	1	Must usually contain the LookupValue. Otherwise, VLOOKUP usually returns the #N/A error.
Second	2
Third	3
…	…
#th	#

Item: RangeLookup

The range_lookup argument of the VLOOKUP function (RangeLookup) specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of LookupTable.

• Set RangeLookup to TRUE when searching for an approximate match.
• Set RangeLookup to FALSE when searching for an exact match.

Formula #2: ISERROR(VLOOKUP(…))

Item: ISERROR

The ISERROR function:

• Tests whether an expression (VLOOKUP(…)) returns an error; and
• Returns:
  • TRUE if VLOOKUP(…) returns an error; or
  • FALSE otherwise.

The ISERROR function identifies the following errors:

• #N/A.
• #VALUE!
• #REF!
• #DIV/0!
• #NUM!
• #NAME?
• #NULL!

Item: VLOOKUP(…)

The value argument of the ISERROR function (VLOOKUP(…)) is a value, formula, expression or reference that Excel checks for errors.

If VLOOKUP(…) doesn't return an error, IF returns the result of that formula. For the explanation of these IF and VLOOKUP functions, please refer to the appropriate sections in this Tutorial.

One of the most common errors returned by the VLOOKUP function is #N/A. The VLOOKUP function usually returns an #N/A error when you either:

1. Give an inappropriate value (including a value that isn't found in the first column of LookupTable) for the lookup_value argument (LookupValue); or
2. Use the function to locate a value in a table (LookupTable) that isn't properly sorted.

Formula #3: IF(ISERROR(…),ValueIfError,VLOOKUP(…))

Item: IF

The IF function:

• Tests whether a condition (ISERROR(…)) is met; and
• Returns:
  • One value (ValueIfError) if the condition (ISERROR(…)) is met and returns TRUE; and
  • Another value (VLOOKUP(…)) if the condition (ISERROR(…)) isn't met and returns FALSE.

Item: ISERROR(…)

The logical_test argument of the IF function (ISERROR(…)) is the condition Excel tests and evaluates to either:

• TRUE; or
• FALSE.

For the explanation of this ISERROR function, please refer to the appropriate section in this Tutorial.

Item: ValueIfError

The value_if_true argument of the IF function (ValueIfError) is the value, formula, expression or reference that Excel returns if ISERROR(…) returns TRUE. For the explanation of this ISERROR function, please refer to the appropriate section in this Tutorial.

Item: VLOOKUP(…)

The value_if_false argument of the IF function (VLOOKUP(…)) is the value, formula, expression or reference that Excel returns if ISERROR(…) returns FALSE.

In other words, if VLOOKUP(…) doesn't return an error, IF returns the result of that formula. For the explanation of these IF and VLOOKUP functions, please refer to the appropriate sections in this Tutorial.

IF ISERROR VLOOKUP formula example

The worksheet formula below carries out a VLookup that handles possible errors (with IF ISERROR vs. IFERROR), as follows:

• LookupValue: The value specified in cell M8 ($M$8).
• LookupTable: The lookup table in cells A8 to E57 ($A$8:$E$57).
• ColumnIndex: The column number specified in column K (K11).
• RangeLookup: FALSE.
• ValueIfError: The string “Sales Manager not found” (“Sales Manager not found”).

=IF(ISERROR(VLOOKUP($M$8,$A$8:$E$57,K11,FALSE)),"Sales Manager not found",VLOOKUP($M$8,$A$8:$E$57,K11,FALSE))

Effects of using IF ISERROR VLOOKUP formula example

The following images illustrate the results returned by the IF ISERROR VLOOKUP formula that carries out a VLookup that handles possible errors (with IF ISERROR vs. IFERROR).

The image below displays the LookupTable.

The image below displays the results returned by IF ISERROR VLOOKUP. As expected, the formula in cell M11 does the following:

1. Looks for the value (LookupValue) specified in cell M8 (Shawn Brooks) in the first column (column A) of the lookup table (LookupTable).
2. The LookupValue (Shawn Brooks) isn't found in the first column of LookupTable. Therefore, the IF ISERROR VLOOKUP formula returns “Sales Manager not found”.

Notice the difference between the result returned by the IFERROR VLOOKUP formula that handles errors (cell M11) and the result returned by the regular VLOOKUP formula in cell M9 (#N/A).

#8: ISERR

ISERR formula

To check whether an expression returns an error other than #N/A (with ISERR vs. ISERROR), use a formula with the following structure:

=ISERR(Value)

ISERR process

To check whether an expression returns an error other than #N/A (with ISERR vs. ISERROR), specify the expression (Value) you want to check for errors (other than #N/A).

ISERR formula explanation

Item: ISERR

The ISERR function:

• Tests whether an expression (Value) returns an error (other than the #N/A error); and
• Returns:
  • TRUE if Value returns an error (other than the #N/A error); or
  • FALSE otherwise.

The ISERR function identifies the following errors:

• #VALUE!
• #REF!
• #DIV/0!
• #NUM!
• #NAME?
• #NULL!

ISERR doesn't identify the #N/A error.

Item: Value

The value argument of the ISERR function (Value) is a value, formula, expression or reference that Excel checks for errors (other than the #N/A error).

ISERR formula example

The worksheet formulas below check whether an expression returns an error other than #N/A (with ISERR vs. ISERROR). Value is the value specified in column H (H8 to H57) or N (N9 to N12).

Table	No.	ISERR formula
1	1	=ISERR(H8)
1	2	=ISERR(H9)
1	3	=ISERR(H10)
1	4	=ISERR(H11)
1	5	=ISERR(H12)
1	…	…
1	50	=ISERR(H57)
2	1	=ISERR(N9)
2	2	=ISERR(N10)
2	3	=ISERR(N11)
2	4	=ISERR(N12)

Effects of using ISERR formula example

The following images illustrate the results returned by the ISERR formula that checks whether an expression returns an error other than #N/A (with ISERR vs. ISERROR).

As expected, the formulas:

1. Check an expression (Value) for errors (other than #N/A); and
2. Return the following:
   1. TRUE: If Value returns an error other than #N/A.
   2. FALSE: If Value:
      1. Doesn't return an error; or
      2. Returns #N/A.

The image below displays a table containing certain #DIV/0! errors. Notice that, when Value returns such errors (cells H12 and H22), the ISERR formula returns TRUE (cells I12 and I22).

The image below displays a table containing certain #N/A errors. Notice that, when Value returns such errors (cells N9 to N12), the ISERR formula continues to return FALSE (cells O9 to O12).

#9: IFNA VLOOKUP

IFNA VLOOKUP formula

To carry out a VLookup that handles possible #N/A errors (with IFNA vs. IFERROR), use a formula with the following structure:

=IFNA(VLOOKUP(LookupValue,LookupTable,ColumnIndex,RangeLookup),ValueIfNa)

IFNA VLOOKUP process

To carry out a VLookup that handles possible #N/A errors (with IFNA vs. IFERROR), follow these steps:

1. Specify the value you want to look up (LookupValue) in the first (leftmost) column of a table (LookupTable).
2. Identify the cell range (a table array) containing the lookup table (LookupTable).
3. Specify the index number of the column (within LookupTable) from which you want to obtain a value (ColumnIndex).
4. Specify whether VLOOKUP searches for an exact or approximate match (RangeLookup).
5. Specify that, if VLOOKUP returns the #N/A error (IFNA), another value (ValueIfNa) is returned.

IFNA VLOOKUP formula explanation

Formula #1: VLOOKUP(LookupValue,LookupTable,ColumnIndex,RangeLookup)

Item: VLOOKUP

The VLOOKUP function does the following:

1. Looks for a value (LookupValue) in the first (leftmost) column of a table (LookupTable); and
2. Returns a value in the same row but from another column you specify (ColumnIndex).

Item: LookupValue

The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of LookupTable. In other words, LookupValue must usually be in the first column of the cell range you specify as LookupTable.

If VLOOKUP doesn't find LookupValue in the first column of LookupTable, it usually returns the #N/A error.

You can specify LookupValue as either:

• A value;
• A text string; or
• A cell reference.

Item: LookupTable

The table_array argument of the VLOOKUP function (LookupTable) is the cell range in which VLOOKUP searches for the following:

1. The LookupValue in the first column of LookupTable; and
2. The value to return in the column you specify (ColumnIndex).

Therefore, the cell range you specify as LookupTable must usually include both of the following columns:

• The first column, which must contain the LookupValue; and
• The column from which VLOOKUP should return a value.

If VLOOKUP doesn't find LookupValue in the first column of LookupTable, it usually returns the #N/A error.

Item: ColumnIndex

The col_index_num argument of the VLOOKUP function (ColumnIndex) is the column number within the LookupTable from which VLOOKUP returns a value, as follows:

Column	ColumnIndex	Comments
First	1	Must usually contain the LookupValue. Otherwise, VLOOKUP usually returns the #N/A error.
Second	2
Third	3
…	…
#th	#

Item: RangeLookup

The range_lookup argument of the VLOOKUP function (RangeLookup) specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of LookupTable.

• Set RangeLookup to TRUE when searching for an approximate match.
• Set RangeLookup to FALSE when searching for an exact match.

Formula #2: IFNA(VLOOKUP(…),ValueIfNa)

Item: IFNA

The IFNA function:

• Returns the value you specify (ValueIfNa) if a formula or expression (VLOOKUP(…)) returns the #N/A error; and
• Returns the result of that formula (VLOOKUP(…)) otherwise.

In other words, IFNA does the following:

1. Checks a formula (VLOOKUP(…)).
2. If VLOOKUP(…) returns the #N/A error, IFNA returns a value you specify (ValueIfNa).
3. If VLOOKUP(…) doesn't return the #N/A error, IFNA returns the result of that formula.

Item: VLOOKUP(…)

The value argument of the IFNA function (VLOOKUP(…)) is a value, formula, expression or reference that Excel checks for the #N/A error.

If VLOOKUP(….) doesn't return the #N/A error, IFNA returns the result of that formula. For the explanation of this VLOOKUP function, please refer to the appropriate section in this Tutorial.

The VLOOKUP function usually returns an #N/A error when you either:

1. Give an inappropriate value (including a value that isn't found in the first column of LookupTable) for the lookup_value argument (LookupValue); or
2. Use the function to locate a value in a table (LookupTable) that isn't properly sorted.

Item: ValueIfNa

The value_if_na argument of the IFNA function (ValueIfNa) is the value, formula, expression or reference that Excel returns if the value argument of the IFNA function (VLOOKUP(…)) evaluates to #N/A.

IFNA VLOOKUP formula example

The worksheet formula below carries out a VLookup that handles possible #N/A errors (with IFNA vs. IFERROR), as follows:

• LookupValue: The value specified in cell M8 ($M$8).
• LookupTable: The lookup table in cells A8 to E57 ($A$8:$E$57).
• ColumnIndex: The column number specified in column K (K12).
• RangeLookup: FALSE.
• ValueIfNa: The string “Sales Manager not found” (“Sales Manager not found”).

=IFNA(VLOOKUP($M$8,$A$8:$E$57,K12,FALSE),"Sales Manager not found")

Effects of using IFNA VLOOKUP formula example

The following images illustrate the results returned by the IFNA VLOOKUP formula that carries out a VLookup and handles possible #N/A errors (with IFNA vs. IFERROR).

The image below displays the LookupTable.

The image below displays the results returned by IFNA VLOOKUP. As expected, the formula in cell M12 does the following:

1. Looks for the value (LookupValue) specified in cell M8 (Shawn Brooks) in the first column (column A) of the lookup table (LookupTable).
2. The LookupValue (Shawn Brooks) isn't found in the first column of LookupTable. Therefore, the IFNA VLOOKUP formula returns “Sales Manager not found”.

Notice the difference between the result returned by the IFNA VLOOKUP formula that replaces the #N/A error (cell M12) and the result returned by the regular VLOOKUP formula in cell M9 (#N/A).

#10: IFNA then 0

IFNA then 0 formula

To return 0 if an expression returns the #N/A error (with IFNA vs. IFERROR), use a formula with the following structure:

=IFNA(Value,0)

IFNA then 0 process

To return 0 if an expression returns the #N/A error (with IFNA vs. IFERROR), follow these steps:

1. Specify the expression you want to check for the #N/A error (Value).
2. Specify that, if Value returns #N/A (IFNA), 0 (0) is returned.

IFNA then 0 formula explanation

Item: IFNA

The IFNA function:

• Returns the value you specify (0) if an expression (Value) returns the #N/A error; and
• Returns the result of that expression (Value) otherwise.

In other words, IFNA does the following:

1. Checks an expression (Value).
2. If Value returns the #N/A error, IFNA returns a value you specify (0).
3. If Value doesn't return the #N/A error, IFNA returns the result of that expression.

Item: Value

The value argument of the IFNA function (Value) is a value, formula, expression or reference that Excel checks for the #N/A error.

If Value doesn't return the #N/A error, IFNA returns the result of that expression.

Item: 0

The value_if_na argument of the IFNA function (0) is the value, formula, expression or reference that Excel returns if the value argument of the IFNA function (Value) evaluates to #N/A.

To return 0 if an expression (Value) returns the #N/A error, set value_if_na to 0.

IFNA then 0 formula example

The worksheet formula below carries out an exact match VLookup and returns 0 if the VLOOKUP function returns the #N/A error (with IFNA vs. IFERROR). Value is the result returned by a VLOOKUP function with the following arguments:

• lookup_value: The value specified in cell M8 ($M$8).
• table_array: The lookup table in cells A8 to E57 ($A$8:$E$57).
• col_index_num: The column number specified in column K (K13).
• range_lookup: FALSE.

=IFNA(VLOOKUP($M$8,$A$8:$E$57,K13,FALSE),0)

Effects of using IFNA then 0 formula example

The following images illustrate the results returned by the IFNA VLOOKUP formula that carries out an exact match VLookup and returns 0 if the VLOOKUP function returns the #N/A error (with IFNA vs. IFERROR).

The image below displays the LookupTable.

The image below displays the results returned by IFNA VLOOKUP. As expected, the formula in cell M13 does the following:

1. Looks for the value (lookup_value) specified in cell M8 (Shawn Brooks) in the first column (column A) of the lookup table (table_array).
2. The lookup value (Shawn Brooks) isn't found in the first column of table_array. Therefore, the IFNA VLOOKUP formula returns 0.

Notice the difference between the result returned by the IFNA VLOOKUP formula that handles the #N/A error by returning 0 (cell M13) and the result returned by the regular VLOOKUP formula in cell M9 (#N/A).

#11: IFNA then blank

IFNA then blank formula

To return a blank if an expression returns the #N/A error (with IFNA vs. IFERROR), use a formula with the following structure:

=IFNA(Value,"")

IFNA then blank process

To return a blank if an expression returns the #N/A error (with IFNA vs. IFERROR), follow these steps:

1. Specify the expression you want to check for the #N/A error (Value).
2. Specify that, if Value returns #N/A (IFNA), a zero-length string (“”) is returned.

IFNA then blank formula explanation

Item: IFNA

The IFNA function:

• Returns the value you specify (“”) if an expression (Value) returns the #N/A error; and
• Returns the result of that expression (Value) otherwise.

In other words, IFNA does the following:

1. Checks an expression (Value).
2. If Value returns the #N/A error, IFNA returns a value you specify (“”).
3. If Value doesn't return the #N/A error, IFNA returns the result of that expression.

Item: Value

The value argument of the IFNA function (Value) is a value, formula, expression or reference that Excel checks for the #N/A error.

If Value doesn't return the #N/A error, IFNA returns the result of that expression.

Item: “”

The value_if_na argument of the IFNA function (“”) is the value, formula, expression or reference that Excel returns if the value argument of the IFNA function (Value) evaluates to #N/A.

To return a blank if an expression (Value) returns the #N/A error, set value_if_na to a zero-length string (“”).

IFNA then blank formula example

The worksheet formula below carries out an exact match VLookup and returns a blank if the VLOOKUP function returns the #N/A error (with IFNA vs. IFERROR). Value is the result returned by a VLOOKUP function with the following arguments:

• lookup_value: The value specified in cell M8 ($M$8).
• table_array: The lookup table in cells A8 to E57 ($A$8:$E$57).
• col_index_num: The column number specified in column K (K14).
• range_lookup: FALSE.

=IFNA(VLOOKUP($M$8,$A$8:$E$57,K14,FALSE),"")

Effects of using IFNA then blank formula example

The following images illustrate the results returned by the IFNA VLOOKUP formula that carries out an exact match VLookup and returns a blank if the VLOOKUP function returns the #N/A error (with IFNA vs. IFERROR).

The image below displays the LookupTable.

The image below displays the results returned by IFNA VLOOKUP. As expected, the formula in cell M14 does the following:

1. Looks for the value (lookup_value) specified in cell M8 (Shawn Brooks) in the first column (column A) of the lookup table (table_array).
2. The lookup value (Shawn Brooks) isn't found in the first column of table_array. Therefore, the IFNA VLOOKUP formula returns a zero-length string (“”).

Notice the difference between the result returned by the IFNA VLOOKUP formula that handles the #N/A error by returning a blank (cell M14) and the result returned by the regular VLOOKUP formula in cell M9 (#N/A).

#12: ISNA

ISNA formula

To check whether an expression returns #N/A (with ISNA vs. ISERROR), use a formula with the following structure:

=ISNA(Value)

ISNA process

To check whether an expression returns #N/A (with ISNA vs. ISERROR), specify the expression (Value) you want to check for the #N/A error.

ISNA formula explanation

Item: ISNA

The ISNA function:

• Tests whether an expression (Value) returns the #N/A error; and
• Returns:
  • TRUE, if Value returns the #N/A error; or
  • FALSE otherwise.

Item: Value

The value argument of the ISNA function (Value) is a value, formula, expression or reference that Excel checks for the #N/A error.

ISNA formula example

The worksheet formula below carries out an exact match VLookup and checks whether the VLOOKUP function returns #N/A (with ISNA vs. ISERROR). Value is the result returned by a VLOOKUP function with the following arguments:

• lookup_value: The value specified in cell M8 ($M$8).
• table_array: The lookup table in cells A8 to E57 ($A$8:$E$57).
• col_index_num: The column number specified in column K (K15).
• range_lookup: FALSE.

=ISNA(VLOOKUP($M$8,$A$8:$E$57,K15,FALSE))

Effects of using ISNA formula example

The following images illustrate the results returned by the ISNA VLOOKUP formula that carries out an exact match VLookup and checks whether the VLOOKUP function returns #N/A (with ISNA vs. ISERROR).

The image below displays the LookupTable.

The image below displays the results returned by ISNA VLOOKUP. As expected, the formula in cell M15 does the following:

1. Looks for the value (lookup_value) specified in cell M8 (Shawn Brooks) in the first column (column A) of the lookup table (table_array).
2. The lookup value (Shawn Brooks) isn't found in the first column of table_array. Therefore, the ISNA VLOOKUP formula returns TRUE.

Notice the difference between the result returned by the ISNA VLOOKUP formula that identifies #N/A errors (cell M15) and the result returned by the regular VLOOKUP formula in cell M9 (#N/A).

#13: IF ISNA VLOOKUP

IF ISNA VLOOKUP formula

To carry out a VLookup that handles possible #N/A errors (with IF ISNA vs. IFNA), use a formula with the following structure:

=IF(ISNA(VLOOKUP(LookupValue,LookupTable,ColumnIndex,RangeLookup)),ValueIfNa,VLOOKUP(LookupValue,LookupTable,ColumnIndex,RangeLookup))

IF ISNA VLOOKUP process

To carry out a VLookup that handles possible #N/A errors (with IF ISNA vs. IFNA), follow these steps:

1. Specify the value you want to look up (LookupValue) in the first (leftmost) column of a table (LookupTable).
2. Identify the cell range (a table array) containing the lookup table (LookupTable).
3. Specify the index number of the column (within LookupTable) from which you want to obtain a value (ColumnIndex).
4. Specify whether VLOOKUP searches for an exact or approximate match (RangeLookup).
5. Test whether VLOOKUP returns the #N/A error (ISNA) and specify (IF) that:
   1. If VLOOKUP returns the #N/A error, another value (ValueIfNa) is returned; and
   2. If VLOOKUP doesn't return the #N/A error, the result of VLOOKUP is returned.

IF ISNA VLOOKUP formula explanation

Formula #1: VLOOKUP(LookupValue,LookupTable,ColumnIndex,RangeLookup)

Item: VLOOKUP

The VLOOKUP function does the following:

1. Looks for a value (LookupValue) in the first (leftmost) column of a table (LookupTable); and
2. Returns a value in the same row but from another column you specify (ColumnIndex).

Item: LookupValue

The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of LookupTable. In other words, LookupValue must usually be in the first column of the cell range you specify as LookupTable.

If VLOOKUP doesn't find LookupValue in the first column of LookupTable, it usually returns the #N/A error.

You can specify LookupValue as either:

• A value;
• A text string; or
• A cell reference.

Item: LookupTable

The table_array argument of the VLOOKUP function (LookupTable) is the cell range in which VLOOKUP searches for the following:

1. The LookupValue in the first column of LookupTable; and
2. The value to return in the column you specify (ColumnIndex).

Therefore, the cell range you specify as LookupTable must usually include both of the following columns:

• The first column, which must contain the LookupValue; and
• The column from which VLOOKUP should return a value.

If VLOOKUP doesn't find LookupValue in the first column of LookupTable, it usually returns the #N/A error.

Item: ColumnIndex

The col_index_num argument of the VLOOKUP function (ColumnIndex) is the column number within the LookupTable from which VLOOKUP returns a value, as follows:

Column	ColumnIndex	Comments
First	1	Must usually contain the LookupValue. Otherwise, VLOOKUP usually returns the #N/A error.
Second	2
Third	3
…	…
#th	#

Item: RangeLookup

The range_lookup argument of the VLOOKUP function (RangeLookup) specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of LookupTable.

• Set RangeLookup to TRUE when searching for an approximate match.
• Set RangeLookup to FALSE when searching for an exact match.

Formula #2: ISNA(VLOOKUP(…))

Item: ISNA

The ISNA function:

• Tests whether an expression (VLOOKUP(…)) returns the #N/A error; and
• Returns:
  • TRUE, if Value returns the #N/A error; or
  • FALSE otherwise.

Item: VLOOKUP(…)

The value argument of the ISNA function (VLOOKUP(…)) is a value, formula, expression or reference that Excel checks for the #N/A error.

If VLOOKUP(…) doesn't return the #N/A error, the IF function returns the result of that formula. For the explanation of these IF and VLOOKUP functions, please refer to the appropriate sections in this Tutorial.

The VLOOKUP function usually returns an #N/A error when you either:

1. Give an inappropriate value (including a value that isn't found in the first column of LookupTable) for the lookup_value argument (LookupValue); or
2. Use the function to locate a value in a table (LookupTable) that isn't properly sorted.

Formula #3: IF(ISNA(…),ValueIfNa,VLOOKUP(…))

Item: IF

The IF function:

• Tests whether a condition (ISNA(…)) is met; and
• Returns:
  • One value (ValueIfNa) if the condition (ISNA(…)) is met and returns TRUE; and
  • Another value (VLOOKUP(…)) if the condition (ISNA(…)) isn't met and returns FALSE.

Item: ISNA

The logical_test argument of the IF function (ISNA(…)) is the condition Excel tests and evaluates to either:

• TRUE; or
• FALSE.

For the explanation of this ISNA function, please refer to the appropriate section in this Tutorial.

Item: ValueIfNa

The value_if_true argument of the IF function (ValueIfNa) is the value, formula, expression or reference that Excel returns if ISNA(…) returns TRUE. For the explanation of this ISNA function, please refer to the appropriate section in this Tutorial.

Item: VLOOKUP(…)

The value_if_false argument of the IF function (VLOOKUP(…)) is the value, formula, expression or reference that Excel returns if ISNA(…) returns FALSE.

In other words, if VLOOKUP(…) doesn't return the #N/A error, IF returns the result of that formula. For the explanation of these IF and VLOOKUP functions, please refer to the appropriate sections in this Tutorial.

IF ISNA VLOOKUP formula example

The worksheet formula below carries out an exact match VLookup and handles possible #N/A errors (with IF ISNA vs. IFNA), as follows:

• LookupValue: The value specified in cell M8 ($M$8).
• LookupTable: The lookup table in cells A8 to E57 ($A$8:$E$57).
• ColumnIndex: The column number specified in column K (K16).
• RangeLookup: FALSE.
• ValueIfNa: The string “Sales Manager not found” (“Sales Manager not found”).

=IF(ISNA(VLOOKUP($M$8,$A$8:$E$57,K16,FALSE)),"Sales Manager not found",VLOOKUP($M$8,$A$8:$E$57,K16,FALSE))

Effects of using IF ISNA VLOOKUP formula example

The following images illustrate the results returned by the IF ISNA VLOOKUP formula that carries out a VLookup and handles possible #N/A errors (with IF ISNA vs. IFNA).

The image below displays the LookupTable.

The image below displays the results returned by IF ISNA VLOOKUP. As expected, the formula in cell M16 does the following:

1. Looks for the value (LookupValue) specified in cell M8 (Shawn Brooks) in the first column (column A) of the lookup table (LookupTable).
2. The LookupValue (Shawn Brooks) isn't found in the first column of LookupTable. Therefore, the IF ISNA VLOOKUP formula returns “Sales Manager not found”.

Notice the difference between the result returned by the IF ISNA VLOOKUP formula that replaces the #N/A error (cell M16) and the result returned by the regular VLOOKUP formula in cell M9 (#N/A).

#14: ISNA MATCH

ISNA MATCH formula

To check whether a specific value exists in a list or compare 2 columns (with ISNA MATCH), use a formula with the following structure:

=ISNA(MATCH(IndividualValue,EntireList,0))

ISNA MATCH process

To check whether a specific value exists in a list or compare 2 columns (with ISNA MATCH), follow these steps:

1. Specify the value you want to find (IndividualValue) in the column you use as basis for comparison.
2. Identify the cell range containing the column you use as basis for comparison (EntireList).
3. Search EntireList for the first value that is exactly equal to IndividualValue (0).
4. Test whether MATCH returns the #N/A error because it doesn't find IndividualValue in EntireList (ISNA).

ISNA MATCH formula explanation

Formula #1: MATCH(IndividualValue,EntireList,0)

Item: MATCH

The MATCH function does the following:

1. Searches for an item (IndividualValue) in a cell range (EntireList); and
2. Returns the relative position of that item (IndividualValue) within the cell range (EntireList).

If MATCH doesn't find IndividualValue in EntireList, it returns the #N/A error. In other words, if IndividualValue isn't in EntireList, MATCH returns #N/A.

Item: IndividualValue

The lookup_value argument of the MATCH function (IndividualValue) is the value you look for in the column you use as basis for comparison (EntireList). In other words, IndividualValue is the value you want to find (and confirm that it exists) in EntireList.

You can specify IndividualValue as either:

• A value;
• A text string; or
• A cell reference.

Item: EntireList

The lookup_array argument of the MATCH function (EntireList) is the cell range in which MATCH searches for IndividualValue. In other words, EntireList is the column you use as basis for comparison.

Item: 0

The match_type argument of the MATCH function (0) specifies how the MATCH function matches IndividualValue with the values in EntireList.

Set the match_type argument to 0 when comparing 2 columns with ISNA MATCH. This results in the MATCH function finding the first value that is exactly equal to IndividualValue.

Formula #2: ISNA(MATCH(…))

Item: ISNA

The ISNA function:

• Tests whether an expression (MATCH(…)) returns the #N/A error; and
• Returns:
  • TRUE, if MATCH(…) returns the #N/A error; or
  • FALSE otherwise.

In other words, ISNA does the following:

1. Checks a formula (MATCH(…)).
2. If MATCH(…) returns the #N/A error, ISNA returns TRUE.
3. If MATCH(…) doesn't return the #N/A error, ISNA returns FALSE.

Item: MATCH(…)

The value argument of the ISNA function (MATCH(…)) is a value, formula, expression or reference that Excel checks for the #N/A error.

The MATCH function returns an #N/A error when IndividualValue isn't found in EntireList. For the explanation of this MATCH function, please refer to the appropriate section in this Tutorial.

ISNA MATCH formula example

The worksheet formulas below check whether a specific value exists in a list or compare 2 columns (with ISNA MATCH), as follows:

• IndividualValue: The value specified in column G (G7 to G56).
• EntireList: The list/column in cells A7 to A48 ($A$7:$A$48).

No.	ISNA MATCH formula
1	=ISNA(MATCH(G7,$A$7:$A$48,0))
2	=ISNA(MATCH(G8,$A$7:$A$48,0))
3	=ISNA(MATCH(G9,$A$7:$A$48,0))
4	=ISNA(MATCH(G10,$A$7:$A$48,0))
5	=ISNA(MATCH(G11,$A$7:$A$48,0))
…	…
50	=ISNA(MATCH(G56,$A$7:$A$48,0))

Effects of using ISNA MATCH formula example

The following image illustrates the results returned by the ISNA MATCH formula that checks whether a value exists in a list or compares 2 columns. As expected, the formulas (in cells L7 to L18):

1. Check whether the value in column G (IndividualValue) is in column A (EntireList).
2. Return the following:
   1. TRUE: If IndividualValue isn't found in EntireList.
   2. FALSE: If IndividualValue is found in EntireList.

Notice that, when IndividualValue isn't found in EntireList (cells G12 and G18), the ISNA MATCH formula returns TRUE (cells L12 and L18).

Workbook example used in this Excel IFERROR, ISERROR, ISERR, IFNA and ISNA Tutorial

You can get immediate free access to the example workbook that accompany this Excel IFERROR, ISERROR, ISERR, IFNA and ISNA Tutorial by subscribing to the Power Spreadsheets Newsletter.