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*

## Leave a Comment