Match first error

The Excel MATCH function is used to locate the position of a lookup value in a row, column or a table. The Excel ISERROR function returns TRUE for any error type excel generates, including #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! In Excel to match first error, you can probably use an array formula that is based on the ISERROR and MATCH functions.  

Formula

=MATCH(TRUE,ISERROR(range),0)

Explanation

The formula incorporating Excel MATCH and ISERROR functions to match first error uses the following argument:

  • range (required): It can be a row, column, list or a table in which we have to match first error.

This works as follows:

  • The ISERROR function gives out a TRUE when the value is an error that is recognized and FALSE if not. When it is provided with a range of cells (or an array of cells), the ISERROR function will return an array of TRUE or FALSE values.
  • The MATCH function is set up to match TRUE in the exact match mode. It will find the first TRUE value in the array created by ISERROR and return its position. If no match is found, the MATCH function itself returns #N/A.

Example 1

In this example, we have a list of random values. To match the first error, the following formula is used using Excel MATCH and ISERROR functions;

=MATCH(TRUE,ISERROR(B5:B12),0)

This works as follows:

  • The ISERROR function gives out a TRUE when the value is an error that is recognized and FALSE if not. Here it is given an array of cells so ISERROR function will return an array of TRUE or FALSE results.
  • The MATCH function is set up to match TRUE in the exact match mode. It will find the first TRUE value in the array created by ISERROR and return its position. In this case, it is 5.

Figure 1. Example of how to match the first error in Excel 1

Example 2

In the above example, the formula will match any error. If you wish to match the first #N/A error, simply substitute ISNA in place of ISERROR. We can do that in the following way;

=MATCH(TRUE,ISNA(B5:B12),0)

This works as follows:

  • The ISNA function returns TRUE when a value is a recognized error (#N/A) and FALSE if not. Here it is given an array of cells so ISNA function will return an array of TRUE/FALSE results.
  • The MATCH function is configured to match TRUE in exact match mode. It finds the first TRUE in the array created by ISNA and returns the position. In this case, it is 3.

Figure 2. Example of how to match the first error in Excel 2

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar