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.
Finding several words or phrases
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”.
Finding 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)
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)).
Partial match: contains, begins with, ends with
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
- 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)
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)
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
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)))
And all cells that contain “sofa” or “couch”:
=FILTER(A2:A100,ISNUMBER(SEARCH("sofa",A2:A100))+ISNUMBER(SEARCH("couch",A2:A100)))
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:
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
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.