ThisCalculator

Everyday

Excel: Get All Matches If Cell Contains Text

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 extract every matching value from a list when a cell contains a specific text or number, and how to return all matches, not just the first one. You will learn the formulas, how they work, and how to adapt them to your data.

Formula

To get all matches, you need an array formula that checks each cell in the range and returns the row numbers where the condition is true. The most common approach uses the IF and SMALL functions together:

=IFERROR(INDEX($A$2:$A$100, SMALL(IF($B$2:$B$100="criteria", ROW($A$2:$A$100)-ROW($A$2)+1), ROW()-1)), "")

Here, $A$2:$A$100 is the range from which you want to return values, and $B$2:$B$100 is the range where you check for the condition (e.g., contains “apple”). The ROW() part generates relative row numbers, and SMALL extracts the k-th smallest matching row number as you drag the formula down.

Worked Example

Suppose you have a list in cells A2:A6: “apple”, “banana”, “apple pie”, “cherry”, “apple cider”. You want all cells that contain “apple”.

Enter the formula in cell C2, press Ctrl+Shift+Enter (or just Enter in Excel 365), and drag down:

=IFERROR(INDEX($A$2:$A$6, SMALL(IF(ISNUMBER(SEARCH("apple", $A$2:$A$6)), ROW($A$2:$A$6)-ROW($A$2)+1), ROW()-1)), "")

How it works:

  • SEARCH finds “apple” anywhere in the cell (case-insensitive).
  • ISNUMBER converts the result to TRUE/FALSE.
  • IF returns the relative row number when TRUE, otherwise FALSE.
  • SMALL picks the 1st, 2nd, 3rd… smallest row number as you drag down.
  • INDEX returns the actual value from column A.

Result in C2:C4 will be “apple”, “apple pie”, “apple cider”.

Using FILTER in Excel 365

If you have Excel 365 or Excel 2021, the FILTER function is the simplest way to get all matches:

=FILTER($A$2:$A$100, ISNUMBER(SEARCH("apple", $A$2:$A$100)), "No matches")

This returns a dynamic array that spills all matching values automatically. You don’t need to drag anything. For an exact match, use =FILTER($A$2:$A$100, $A$2:$A$100="apple").

How to Choose the Right Formula

  • Exact match (cell equals “apple”): use FILTER with = or the older INDEX/SMALL with IF(range="apple",...).
  • Contains text (cell has “apple” anywhere): use SEARCH (case-insensitive) or FIND (case-sensitive) inside ISNUMBER.
  • Multiple conditions (e.g., contains “apple” and price > 5): use FILTER with * for AND or + for OR.
  • Older Excel versions (2019 and earlier): use the INDEX/SMALL array formula and confirm with Ctrl+Shift+Enter.

Common Problems and Solutions

#VALUE! error: This often happens when you don’t enter the formula as an array formula in older Excel. Press Ctrl+Shift+Enter after typing it.

Blanks in results: The IFERROR wrapper returns an empty string when there are no more matches. If you see 0s, check that your range doesn’t include empty cells.

Case sensitivity: SEARCH ignores case, FIND does not. Choose based on your needs.

Wrong row numbers: If your data doesn’t start at row 1, the ROW()-1 part may be off. Adjust the subtraction to match your starting row.

Alternatives to Formulas

If you prefer not to use formulas, you can use Excel’s built-in Filter feature: select your data, go to the Data tab, click Filter, then use the search box in the dropdown to filter rows that contain a specific text. This is quick for one-off tasks but doesn’t update automatically.

Another option is Power Query (Get & Transform): load your table, filter rows with a text contains condition, and load the result back. This is useful for large datasets that need refreshing.

Frequently Asked Questions

How do I get all matches in a column without dragging? Use the FILTER function if you have Excel 365. It spills results automatically. In older versions, you need to drag the formula down, but you can pre-fill many rows with IFERROR to hide extras.

Can I return matches from multiple columns? Yes. Use FILTER with a condition that checks multiple columns, e.g., =FILTER(A2:C100, ISNUMBER(SEARCH("apple", A2:A100))) returns entire rows.

Why does my formula return 0 instead of a blank? This happens when the cell format is General and you return an empty string. Change the cell format to Text, or wrap the formula in IFERROR(...,"") and make sure the range is correct.

Is there a way to get matches based on a partial match in Google Sheets? Yes, Google Sheets has FILTER and REGEXMATCH for partial matches: =FILTER(A2:A100, REGEXMATCH(A2:A100, "apple")).

How do I count all matches? Use COUNTIF for exact counts: =COUNTIF(A2:A100, "*apple*") for contains. For multiple conditions, use COUNTIFS.

Related

Formula used
in list | List of values | - | 5, 12, 7, 12, 9, 12 | 0
in lookup | Value to match | - | 12 | 0
out count | Count of matches | - | count(list) - count(list - lookup) | 0
out first_match | First match position | - | (list - lookup) | 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.