## 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.

#### How to locate the first match that does not contain the string “TOOL”?

To find the position of the first value that does not contain the string “TOOL” in the above list, you can use the SEARCH and ISNUMBER functions to generate a TRUE and FALSE array, in which a TRUE represents a value that contains the string “TOOL”, and a FALSE represents a value that does not contain the string. The MATCH function will then match the first FALSE in the array to return the position of the first value that does not contain the string “TOOL”.

#### Generic syntax

=MATCH(FALSE,ISNUMBER(SEARCH(lookup_value,range)),0)

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

• lookup_value: The string you specified to match the first value that does not contain it. The string should be enclosed in double quotes, otherwise the result will go wrong. Or you can use a cell reference to avoid adding double quotes.
• range: The list from which to return the first value that does not contain the lookup_value.
• 0: The match_type 0 forces MATCH to perform an exact match.

To find the position of the first value that does not contain the string “TOOL”, please copy or enter the formula below in the cell E5, and press Ctrl + Shift + Enter to get the result:

=MATCH(FALSE,ISNUMBER(SEARCH("TOOL",B5:B11)),0)

Or, use a cell reference to make the formula dynamic:

=MATCH(FALSE,ISNUMBER(SEARCH(E4,B5:B11)),0)

#### Explanation of the formula

=MATCH(FALSE,(ISNUMBER(SEARCH(E4,B5:B11)),0)

• SEARCH(E4,B5:B11): The SEARCH function searches for the string TOOL (value in E4) in each value in the range B5:B11, and returns the numbers of the starting position of the string in those values in an array: {3;3;4;#VALUE!;1;3;#VALUE!}.
• ISNUMBER(SEARCH(E4,B5:B11)) = ISNUMBER({3;3;4;#VALUE!;1;3;#VALUE!}): ISNUMBER turns the numbers in the array to TRUEs, and other values to FALSEs. So, the result will be like this: {TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE}.
• MATCH(FALSE,(ISNUMBER(SEARCH(E4,B5:B11)),0) = MATCH(FALSE,({TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;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 FALSE in the array. So, the function will return 4 since the first FALSE is at the 4th position.

#### 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.

Excel SEARCH function

In Excel, the SEARCH function can help you to find the position of a specific character or substring from the given text string as following screenshot shown. This tutorial, I will introduce how to use this SEARCH function in Excel.

#### 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 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 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

 🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions… Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ... Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup... Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List... Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Columns to Select Same & Different Cells ... Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ... Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

#### 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, Edge and Firefox.