Excel FILTER Multiple Criteria: Partial Match, OR/AND, Date Ranges, and Remove Duplicates
Summary: FILTER is the standard for lookups in the dynamic-array era. Combine criteria arrays of the same length with * (AND) and + (OR), and even complex filters can be completed in one line. Use SEARCH for partial matches, comparison operators for dates, and UNIQUE and SORTBY for post-processing to quickly build a practical dashboard.
1) Basic Syntax and Principles
The syntax is =FILTER(return_array, include, [if_empty]). include contains a TRUE/FALSE array with the same length as the return range. Only TRUE values remain, while FALSE values are removed and the results spill dynamically. When you use a table, the range expands automatically, so you do not need to revise the formula as data grows.
2) Multiple AND Criteria
=FILTER(SalesTbl, (SalesTbl[Region]="서울")*(SalesTbl[Score]>=90)*(SalesTbl[Status]<>"취소"))
Multiplication (*) creates AND logic because only TRUE (1) × TRUE (1) = 1 passes through. The comparison operator <> means “not equal to.”
3) Region/Category OR
=FILTER(SalesTbl, (SalesTbl[Region]="서울")+(SalesTbl[Region]="부산"))
With addition (+), if any condition is TRUE, the result becomes 1 and satisfies the OR condition. When you have three or more OR criteria, the ISNUMBER(XMATCH()) pattern is easier to read.
=FILTER(SalesTbl, ISNUMBER(XMATCH(SalesTbl[Region], {"서울","부산","대전"})))4) Partial-Match (Contains) Search
=FILTER(SalesTbl, ISNUMBER(SEARCH("프리미엄", SalesTbl[Product])))
SEARCH is not case-sensitive. Use FIND when you need case sensitivity. For OR logic with multiple keywords, combine them with addition, such as ISNUMBER(SEARCH("A",…))+ISNUMBER(SEARCH("B",…)).
5) Date Range + Sorting + Removing Duplicates
=LET(
s, DATE(2025,1,1),
e, DATE(2025,12,31),
f, FILTER(SalesTbl, (SalesTbl[Date]>=s)*(SalesTbl[Date]<=e)*(SalesTbl[Qty]>0)),
SORTBY( UNIQUE(CHOOSECOLS(f, XMATCH("Customer", SalesTbl[#Headers]))),
CHOOSECOLS(f, XMATCH("Qty", SalesTbl[#Headers])), -1 )
)
This example returns unique customers who purchased within the date range and sorts them by quantity in descending order. LET improves both readability and performance.
6) Control Empty Results/Error Messages
=IFERROR(
FILTER(SalesTbl, (SalesTbl[Region]="서울")*(SalesTbl[Qty]>0)),
"No results match the criteria."
)7) Performance and Maintenance Tips
- Separating criteria columns into helper columns makes maintenance easier. Example:
Helper_AND = (Region="서울")*(Qty>0) - Convert large ranges to tables with Ctrl+T, and limit calculation ranges to only needed columns with
CHOOSECOLS. - If
NA()values are present, comparisons fail. Clean them up withIFERRORbefore filtering.