How to find errors in a list

by | Jun 7, 2018 | Excel Tips & Tricks

How to find first error in a list

This article shows how to find errors in a list by using ISERROR and MATCH functions in an array formula.

Syntax

{ =MATCH(TRUE, ISERROR(search array), 0) }

Steps

  1. Start with =MATCH( function
  2. Type TRUE, variable to search for the matched values
  3. Continue with ISERROR( function
  4. Select the reference that contains the range to be searched C3:C11
  5. Type ) to close the ISERROR function
  6. Add 0 to ensure exact match
  7. Type ) to close the MATCH function and complete the formula
  8. Click Ctrl + Shift + Enter instead of Enter to make it an array formula

How

By default, you can't type an error in a formula and use it as an argument. Although #N/A error can be produced by NA function, it can't be used either. Because if there is an error in function, the function returns an error as well. There are few functions that can behave contrary to this assumption though. Functions like ISERROR or IFERROR accept errors as an argument. Also, some functions like COUNT or SUM ignore "only" #N/A errors.

We can use the ISERROR function to create an array and the MATCH function to locate the errors in the generated array. There are 2 tricks to apply here:

  1. Make the ISERROR function to return an array which is handled automatically when we define it as an array formula.
  2. Search TRUE values, instead of search value itself, in the array that is returned by the ISERROR function

Let's analyze the formula.

ISERROR(C3:C11) formula returns {FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE} array for the list where the 5th value is an error. By defining the formula as an array we ensured the evaluation of the ISERROR function for each cell in C3:C11 range.

It is now straightforward to find the exact match using the MATCH function among TRUE/FALSE values.

=MATCH(TRUE,ISERROR(C3:C11),0)