Finding several values in Excel in one go is not a trivial task. The AutoFilter’s Custom Filter dialog accepts at most two conditions. They can be completely different, but there are only two of them. And if you want 3, 4, 5 or more search conditions, it doesn’t fit.
Usually this is needed for text criteria: equals, begins with, contains, ends with. For numbers, two conditions (“greater than” and “less than”) are usually enough. Let’s look at the ways to find cells by several conditions. This page is part of the article on how to find cells in Excel.
Find several words or phrases in cells
If the conditions are whole words rather than any substrings, see the separate articles: find words in cells and find phrases in Excel. Words need different logic: “cat” shouldn’t match “category”.
Find several values by exact match
To check whether a cell equals one of several values, a simple comparison in an array formula does the job. The list of values is in E1:E4:
=OR(A1=$E$1:$E$4)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | City | In the list? | List | ||
| 2 | Paris | TRUE=OR(A2=$E$2:$E$5) | London | ||
| 3 | paris | TRUE=OR(A3=$E$2:$E$5) | Paris | ||
| 4 | Madrid | FALSE=OR(A4=$E$2:$E$5) | Berlin | ||
| 5 | Rome | FALSE=OR(A5=$E$2:$E$5) | Rome |
“paris” matches because the comparison ignores case. “Rome ” with a trailing space doesn’t — clean such data with TRIM first.
How it works:
- A1=$E$1:$E$4 compares A1 with every value in the list and returns an array of TRUE and FALSE;
- the dollar signs make the reference absolute, so it doesn’t shift when you fill the formula down;
- the OR function returns TRUE if at least one comparison is TRUE.
This formula is especially useful for checking whether a value belongs to a list of allowed values. The comparison ignores case; if case matters, use the EXACT function instead: =OR(EXACT(A1,$E$1:$E$4)).
Find cells by partial match: contains, begins, ends
If you need a partial match rather than an exact one, the formula depends on the type of search. The functions SEARCH and FIND work for “contains”. The only difference between them is that FIND is case-sensitive and SEARCH is not.
Contains any of the values
=COUNT(SEARCH($B$1:$B$10,A1))>0
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | Result | List | |
| 2 | monthly report | TRUE=COUNT(SEARCH($D$2:$D$4,A2))>0 | report | |
| 3 | plan B | TRUE=COUNT(SEARCH($D$2:$D$4,A3))>0 | plan | |
| 4 | project report | TRUE=COUNT(SEARCH($D$2:$D$4,A4))>0 | task | |
| 5 | taskbar | TRUE=COUNT(SEARCH($D$2:$D$4,A5))>0 | ||
| 6 | budget | FALSE=COUNT(SEARCH($D$2:$D$4,A6))>0 |
SEARCH returns a position for each value found and an error for the rest; COUNT counts the positions. “taskbar” contains “task”, so it matches.
- SEARCH($B$1:$B$10,A1) looks for each value of the list in A1 and returns an array of positions and errors;
- COUNT counts the numbers in it, that is, the values found;
- >0 returns TRUE if at least one value was found.
Important: the list must not contain empty cells. SEARCH finds an empty string in any text, and the formula returns TRUE for every cell. The same approach lets you find uppercase letters or find numbers in text cells.
Begins with any of the values
To check whether a cell begins with at least one of the values in the list, compare the beginning of the cell of the right length with each value:
=OR(LEFT(A1,LEN($B$1:$B$10))=$B$1:$B$10)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | Result | List | |
| 2 | monthly report | FALSE=OR(LEFT(A2,LEN($D$2:$D$4))=$D$2:$D$4) | report | |
| 3 | plan B | TRUE=OR(LEFT(A3,LEN($D$2:$D$4))=$D$2:$D$4) | plan | |
| 4 | project report | FALSE=OR(LEFT(A4,LEN($D$2:$D$4))=$D$2:$D$4) | task | |
| 5 | taskbar | TRUE=OR(LEFT(A5,LEN($D$2:$D$4))=$D$2:$D$4) | ||
| 6 | budget | FALSE=OR(LEFT(A6,LEN($D$2:$D$4))=$D$2:$D$4) |
LEFT cuts as many characters as each value has and compares them. “project report” doesn’t begin with a value from the list.
LEN takes the length of each value, LEFT cuts that many characters from A1, and the comparison checks whether they match. For example, with the list “report”, “plan”, “task”, the cell “Report on project” returns TRUE, and “project report” returns FALSE.
Ends with any of the values
The same logic with RIGHT:
=OR(RIGHT(A1,LEN($B$1:$B$10))=$B$1:$B$10)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | Result | List | |
| 2 | monthly report | TRUE=OR(RIGHT(A2,LEN($D$2:$D$4))=$D$2:$D$4) | report | |
| 3 | plan B | FALSE=OR(RIGHT(A3,LEN($D$2:$D$4))=$D$2:$D$4) | plan | |
| 4 | project report | TRUE=OR(RIGHT(A4,LEN($D$2:$D$4))=$D$2:$D$4) | task | |
| 5 | taskbar | FALSE=OR(RIGHT(A5,LEN($D$2:$D$4))=$D$2:$D$4) | ||
| 6 | budget | FALSE=OR(RIGHT(A6,LEN($D$2:$D$4))=$D$2:$D$4) |
Now “monthly report” and “project report” match; “taskbar” doesn’t end with “task”.
Now “project report” returns TRUE. You’ll find formulas built on SEARCH for this task on the web, but they fail when the value occurs in the text more than once, as in “reportreport”. The RIGHT version doesn’t have this problem.
In Excel 2019 and earlier, all these formulas are entered with Ctrl+Shift+Enter. In Excel 365 and 2021, just press Enter.
Checking a whole range for several values
To find out whether a range contains at least one of several values, COUNTIF with a list as its criteria does the job:
=SUMPRODUCT(COUNTIF($A$1:$A$100,$B$1:$B$10))>0
COUNTIF supports wildcards, so the same formula finds cells that begin or end with any of the values:
=SUMPRODUCT(COUNTIF($A$1:$A$100,$B$1:$B$10&"*"))>0
=SUMPRODUCT(COUNTIF($A$1:$A$100,"*"&$B$1:$B$10))>0
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Data | List | Check | Result | ||
| 2 | monthly report | report | equals any | FALSE=SUMPRODUCT(COUNTIF($A$2:$A$5,$C$2:$C$4))>0 | ||
| 3 | plan B | plan | begins with any | TRUE=SUMPRODUCT(COUNTIF($A$2:$A$5,$C$2:$C$4&"*"))>0 | ||
| 4 | taskbar | task | ends with any | TRUE=SUMPRODUCT(COUNTIF($A$2:$A$5,"*"&$C$2:$C$4))>0 | ||
| 5 | budget |
No cell equals a list value exactly, so the first check is FALSE. “plan B” and “taskbar” begin with list values, and “monthly report” ends with one.
The difference from the previous formulas is that they check a whole range at once, not each cell separately. You can do the same with VLOOKUP or XLOOKUP, but COUNTIF is shorter.
Filtering by several conditions in Excel 365
In Excel 365, the FILTER function returns all the rows that meet any number of conditions. Multiply the conditions for AND, add them for OR. All cells that contain both “red” and “sofa”:
=FILTER(A2:A100,ISNUMBER(SEARCH("red",A2:A100))*ISNUMBER(SEARCH("sofa",A2:A100)))
| A | B | C | |
|---|---|---|---|
| 1 | Product | Result | |
| 2 | Red sofa | Red sofa=FILTER(A2:A6,ISNUMBER(SEARCH("red",A2:A6))*ISNUMBER(SEARCH("sofa",A2:A6))) | |
| 3 | Blue couch | ||
| 4 | Red chair | ||
| 5 | Grey sofa bed | ||
| 6 | Red Couch |
Multiplying the conditions is AND: a row stays only if both words are found.
And all cells that contain “sofa” or “couch”:
=FILTER(A2:A100,ISNUMBER(SEARCH("sofa",A2:A100))+ISNUMBER(SEARCH("couch",A2:A100)))
| A | B | C | |
|---|---|---|---|
| 1 | Product | Result | |
| 2 | Red sofa | Red sofa=FILTER(A2:A6,ISNUMBER(SEARCH("sofa",A2:A6))+ISNUMBER(SEARCH("couch",A2:A6))) | |
| 3 | Blue couch | Blue couch | |
| 4 | Red chair | Grey sofa bed | |
| 5 | Grey sofa bed | Red Couch | |
| 6 | Red Couch |
Adding the conditions is OR. SEARCH ignores case, so “Red Couch” is found too.
In older versions, the Advanced Filter (Data → Advanced) can do something similar: it takes a criteria range, where each row is an OR condition and each column in a row is an AND condition, with wildcards like “*sofa*”.
Find cells by several conditions with !SEMTools
The formulas above handle the task well. But if you solve such tasks often, finding a saved copy of the formula or typing it by hand, filling it down and cleaning up afterwards slows you down a lot.
I’ve been in this situation many times myself, so I decided to automate it and made a few convenient procedures. And then many more for other tasks, and that’s how a whole Excel add-in was born.
With !SEMTools, you search by several conditions without complex formulas: select the range, enter the list of values and choose the type of match (equals, contains, begins with, ends with). The add-in returns TRUE or FALSE next to the values that meet the conditions, right in place:
How to run it:
- Select the column you want to check.
- On the !SEMTools tab, open FIND → Cells → By custom criteria list and pick Containing, Beginning with or Ending with.
- For the last two, choose Input range... and select the cells with your values, or Prepositions to use the built-in list.
- Each cell gets TRUE or FALSE in place, ready for a filter.
See also:
Find cells that equal, contain, begin or end with any value from a list, right in place, without array formulas.
!SEMTools solves this and hundreds of other tasks in a couple of clicks!
Summary: how to find cells by several conditions
I hope you found what you were looking for on this page and were able to find cells by several conditions. OR with a comparison handles exact matches, SEARCH, LEFT and RIGHT handle partial ones, COUNTIF checks a whole range, and FILTER in Excel 365 returns the matching rows. For regular searches by long lists, the !SEMTools procedures are faster.