Note: The other languages of the website are Google-translated. Back to English
English English

Locate first error

To find the position of the first error in a column or row, the MATCH and ISERROR functions can do you a favor.

locate first error 1

How to match the first error in a column or row?

To find the position of the first error in the list as shown above, you can first use the ISERROR function to check each value in the list if they are errors or not. The function then generates a TRUE and FALSE array, in which a TRUE represents a value that is an error, and a FALSE otherwise. The MATCH function will then match the first TURE in the array to return the position of the first error in a numeric value.

Generic syntax

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

√ Note: This is an array formula that requires you to enter with Ctrl + Shift + Enter.

  • range: The one-column or one-row range from which to return the first error.
  • 0: The match_type 0 forces MATCH to perform an exact match.

To match the position of the first error, please copy or enter the formula below in the cell D5, and press Ctrl + Shift + Enter to get the result:

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

locate first error 2

Explanation of the formula

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

  • ISERROR(B5:B14): The ISERROR function checks each value in the range B5:B14 if they are errors or not, returning a TRUE if a value is an error, a FALSE otherwise. The results would be in an array like this: {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}.
  • MATCH(TRUE,ISERROR(B5:B14),0) = MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE},0): The match_type 0 forces the MATCH function to perform an exact match. The function then returns the position of the first exact TRUE in the array. So, the function will return 8 since the first TRUE is at the 8th position.

Things to know

  • To retrieve the error value at the position provide by MATCH, you can add the INDEX function.
  • The ISERROR function in the formula above will match any errors. If you only want to match the first #N/A error, please change the ISERROR to ISNA:
    =MATCH(TRUE,ISNA(range),0)

Related functions

Excel MATCH function

The Excel MATCH function searches for a specific value in a range of cells, and returns the relative position of the value.


Related Formulas

Locate first partial match with wildcards

To get the position of the first partial match that contains specific text string in a range in Excel, you can use a MATCH formula with wildcard characters - the asterisk (*) and question mark (?).

Locate first match that does not begin with

To find the position of the first match that does not begin with a specific string, the MATCH, LEFT and IF functions can do you a favor.

Locate first match that does not contain

To find the position of the first match that does not contain a certain string, you can use a formula based on the MATCH, ISNUMBER and SEARCH functions.

Locate maximum value in a range

There are cases when you need to locate the position of the maximum value in a list, table or row in Excel. In this tutorial, we will discuss the approach to accomplish the task with the MAX and MATCH functions.


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.

  • Designed for 1500 work scenarios, helps you solve 80% Excel problems.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Ribbon of Excel (with Kutools for Excel installed)

Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
Screen Shot of Excel (with Office Tab installed)
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations