Excel: Get All Matches If Cell Contains Text
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:
SEARCHfinds “apple” anywhere in the cell (case-insensitive).ISNUMBERconverts the result to TRUE/FALSE.IFreturns the relative row number when TRUE, otherwise FALSE.SMALLpicks the 1st, 2nd, 3rd… smallest row number as you drag down.INDEXreturns 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
FILTERwith=or the olderINDEX/SMALLwithIF(range="apple",...). - Contains text (cell has “apple” anywhere): use
SEARCH(case-insensitive) orFIND(case-sensitive) insideISNUMBER. - Multiple conditions (e.g., contains “apple” and price > 5): use
FILTERwith*for AND or+for OR. - Older Excel versions (2019 and earlier): use the
INDEX/SMALLarray 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.