ThisCalculator

Everyday

Get First Match Cell Contains in Excel

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This article explains how to find the first cell in a range that contains a specific text or value in Excel, using formulas like INDEX/MATCH with wildcards. You will learn the exact formulas, how they work, and common pitfalls, so you can return the first matching cell’s content or its position.

Formula

The core formula to get the first cell that contains a specific text (substring) is:

=INDEX(range, MATCH(“*”&A1&”*”, range, 0))

  • range – the column or row you are searching (e.g., A2:A100).
  • A1 – the cell with the text you are looking for (or type the text directly in quotes).
  • MATCH – finds the position of the first cell that contains the text. The wildcards * before and after the search text mean “any characters” – so it matches any cell that has your text anywhere inside.
  • INDEX – returns the actual value from that position.

If you only need the position (row number within the range), use just =MATCH(“*”&A1&”*”, range, 0).

Worked Example

Using the default values from the calculator above:

  • List: apple, banana, cherry, date, elderberry
  • Search text: “an”

Step 1: Build the wildcard pattern: *an* (because we add * on both sides).

Step 2: MATCH looks through the list and finds the first cell that contains “an”. “banana” contains “an” (positions: b-a-n-a-n-a, so “an” appears at positions 2-3 and 4-5). “apple” does not, “cherry” does not, “date” does not, but “banana” is the first match. So MATCH returns 2.

Step 3: INDEX returns the value at position 2: “banana”.

Result: banana (position 2).

How It Works: Wildcards and Match Types

Excel’s MATCH function normally looks for an exact match (match type 0). But when you use wildcards (* for any sequence of characters, ? for a single character), it becomes a “contains” search. The formula MATCH("*"&A1&"*", range, 0) tells Excel: find the first cell that has A1’s text anywhere inside it.

Important: Wildcards only work in MATCH, VLOOKUP, HLOOKUP, and COUNTIF with text. They do not work in INDEX directly, so you must combine them.

If your search text itself contains a wildcard character (like * or ?) and you want to treat it literally, you need to escape it with a tilde (~). For example, to find a cell containing “*”, use "*~**".

How to Choose the Right Formula

Depending on what you need, you might use a variation:

  • Get the first matching cell’s value: =INDEX(range, MATCH("*"&A1&"*", range, 0))
  • Get the position (row number): =MATCH("*"&A1&"*", range, 0)
  • Get the first match from the bottom (last match): Use LOOKUP(2,1/ISNUMBER(SEARCH(A1,range)),range) – this returns the last match, not the first.
  • Case-sensitive search: Use INDEX(range, MATCH(TRUE, ISNUMBER(FIND(A1,range)),0)) – this is an array formula (press Ctrl+Shift+Enter in older Excel).
  • Multiple columns: If your data is in a 2D range and you want the first match by row, you may need to use MIN(IF(ISNUMBER(SEARCH(A1,range)),ROW(range)-MIN(ROW(range))+1)) as an array formula.

Common Mistakes and Pitfalls

  • Forgetting the wildcard: If you use MATCH(A1, range, 0) without asterisks, it only finds exact matches, not partial ones.
  • Using the wrong match type: Always use 0 for exact/contains. Using 1 or -1 assumes the data is sorted and can give wrong results.
  • Not handling “no match” errors: If no cell contains the text, the formula returns #N/A. Wrap it in IFERROR: =IFERROR(INDEX(...), "No match").
  • Case sensitivity: MATCH with wildcards is not case-sensitive. If you need case-sensitive, use FIND inside an array formula.
  • Search text with wildcards: If you search for “*”, it matches everything. Use ~* to find a literal asterisk.

Limitations and Alternatives

This formula only returns the first match. If you need all matches, you’ll need a different approach (e.g., FILTER function in Excel 365, or a VBA macro). Also, MATCH with wildcards only works on text – it will not match numbers unless the cell is formatted as text. If your data contains numbers, you may need to convert them to text first (e.g., with TEXT function).

Alternatives for more complex scenarios:

  • FILTER function (Excel 365): =FILTER(range, ISNUMBER(SEARCH(A1,range))) returns all matches dynamically.
  • VLOOKUP with wildcards: =VLOOKUP("*"&A1&"*", range, 1, FALSE) – but it only works if the range is a single column or row, and it returns the first match from the top.
  • Power Query: For large datasets, you can use “Contains” filter in Power Query to find all matching rows.

When to Use This Formula

Use this formula when you need to quickly locate a record based on a partial text match – for example, finding a product by a partial name, a customer by a fragment of an email, or a code that contains a certain substring. It is especially useful in dashboards or reports where you want to pull the first relevant value without manually scanning.

It works well for small to medium-sized datasets (up to tens of thousands of rows) but can slow down if used on very large ranges with array formulas. For massive datasets, consider using Power Query or Excel’s built-in search.

Frequently Asked Questions

How do I get the first cell that contains a specific word, not just any substring?

To match whole words, you need a more complex formula. One way is to use an array formula like =INDEX(range, MATCH(TRUE, ISNUMBER(SEARCH(" "&A1&" ", " "&range&" ")),0)). This adds spaces around both the search text and each cell, so it only matches whole words. Remember to enter it with Ctrl+Shift+Enter in older Excel versions.

Why does my formula return #N/A even though the text exists?

Check if the search text is in a cell that is formatted as a number, or if the range contains numbers. MATCH with wildcards only works on text. Also, ensure the range is a single row or column – if it’s a 2D range, MATCH will only search the first column or row. Use the array formula with ISNUMBER(SEARCH(…)) for 2D ranges.

Can I use this to find the first cell that contains any of several words?

Yes, but it gets complicated. You can use an array formula with OR conditions: =INDEX(range, MATCH(TRUE, ISNUMBER(SEARCH("word1",range))+ISNUMBER(SEARCH("word2",range))>0,0)). This will find the first cell that contains any of the words. For many words, consider using a helper column with COUNTIF.

How do I get the first match from the bottom (last occurrence)?

Use =LOOKUP(2,1/ISNUMBER(SEARCH(A1,range)),range). This returns the last cell in the range that contains the text. It works because dividing 1 by TRUE (1) gives 1, and by FALSE (0) gives #DIV/0!, which LOOKUP ignores. It finds the last valid match.

Does this formula work with numbers?

Not directly. MATCH with wildcards treats numbers as text only if the cell is formatted as text. If your data is numeric, you can convert it to text in the formula: =INDEX(range, MATCH("*"&A1&"*", TEXT(range,"0"), 0)) – but this requires an array formula. Alternatively, use SEARCH with ISNUMBER in an array formula.

Related

Formula used
list vals | Values | apple, banana, cherry, date, elderberry
in find | Search text | an | 0
out first_match | First cell contains | - | INDEX(vals, MATCH("*"&find&"*", vals, 0)) | 0
out position | Position | - | MATCH("*"&find&"*", vals, 0) | 0

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.