Yes. In Excel for Microsoft 365, you can return only the rows in a table whose values appear in a separate list by combining FILTER with COUNTIF. For a reusable result that updates as the source data changes, keep the source records and the match list in separate Excel Tables, then place the formula outside both tables.
Set up the source table and match list
Suppose your source table is named Orders and has columns named Customer, Region, and Amount. A second table, named MatchList, has one column called Item, containing the customer names you want to keep. To create a table, select the data and choose Insert > Table (or press Ctrl+T on Windows); confirm that the selection has headers. Rename a table from Table Design > Table Name.
For this example, imagine the Orders table contains six rows: Mia Chen in East, Omar Ali in West, Nina Patel in East, Mia Chen in South, Leo Kim in West, and Nina Patel in North. If MatchList contains Mia Chen and Nina Patel, the result should contain four source rows: both rows for each of those customers. This is an inclusion test: a row stays if its customer appears at least once in the list.
Put the output formula in a blank cell outside an Excel Table, such as G2. Dynamic-array results need room to spill into neighboring cells, and Excel cannot spill a formula result inside a table. Keep the cells below and to the right of the formula clear, or Excel may report a spill error.
Use FILTER and COUNTIF for a live result
With the source table named Orders, its customer field named Customer, and the allowed names in MatchList[Item], enter this formula in a blank cell outside the tables:
With the records in Orders and the permitted names in MatchList[Item], use: =FILTER(Orders,(Orders[Customer]<>"")*(COUNTIF(MatchList[Item],Orders[Customer])>0),"No matches")
COUNTIF checks each customer in the source against the list. A count greater than zero means the name is present; FILTER returns the corresponding whole rows. The first condition, Orders[Customer]<>"", prevents blank customer cells from being included if the list contains blanks. The formula returns four rows for the example above. It returns the table’s data columns, but not its header row, so add or copy headers above the output if you need them for a report.
This is a dynamic result, not a change to the original table. Add a matching order or change a name in MatchList and the displayed results recalculate. Because MatchList is a table, adding a name in the row immediately below it expands the table and updates the reference. The formula requires Excel for Microsoft 365 or Excel 2021 or later, which support FILTER; older perpetual versions may not recognize it.
Use XMATCH when you want an explicit membership test
An alternative is to use XMATCH to look for each source name in the list. It is useful when the logic is easier to read as “keep rows whose customer has a match.” With the same Orders and MatchList tables, enter:
With customer names in Orders[Customer] and allowed names in MatchList[Item], use: =FILTER(Orders,(Orders[Customer]<>"")*ISNUMBER(XMATCH(Orders[Customer],MatchList[Item])),"No matches")
XMATCH returns a position when it finds a name and an error when it does not; ISNUMBER turns that into TRUE or FALSE for FILTER. XMATCH uses exact matching by default, so this version avoids a common surprise with COUNTIF: COUNTIF treats the characters * and ? in criteria as wildcards. For ordinary names and IDs, either approach is usually suitable. XMATCH, like FILTER, is available in Microsoft 365 and Excel 2021 or later.
Filter the original table with a helper column
If you need to work with the source records in place rather than create a separate spilled copy, add a column to Orders named Include?. In the first data cell of that table column, enter a formula that checks the current row against MatchList. Excel fills a calculated column down the table automatically.
With the formula entered in the Include? column of the Orders table, and permitted names in MatchList[Item], use: =COUNTIF(MatchList[Item],[@Customer])>0
The helper column displays TRUE for included customers and FALSE for the rest. Open the filter arrow in the Include? header and select TRUE. To turn the filter on or off, use Data > Filter on the Windows ribbon; the table’s header filter arrows are also available after you create a table. This method hides nonmatching records in the original view rather than generating a second copy, and other table columns remain available for sorting and editing.
A helper column is handy when coworkers need to see why a row is included, or when you want to apply additional ordinary table filters, such as a date range. It is also compatible with Excel versions that do not have FILTER, because COUNTIF is available in older versions. If the match list is a plain cell range rather than a table, adjust the formula’s list reference accordingly and make sure it covers the expected entries.
Use Advanced Filter for a one-time extraction
Excel’s built-in Advanced Filter can extract rows without a dynamic-array formula. It is useful for a one-off copy or for a workbook where formulas are not wanted, but it is less convenient for a list that changes frequently. On Windows desktop Excel, prepare a criteria range with the exact source-column header above the allowed values. For example, put Customer in one cell and Mia Chen and Nina Patel in cells directly beneath it. Values under one criteria header act as alternatives: a row can match either name.
Select a cell in the source data, then choose Data > Advanced. Check that the List range covers the source table, including its header row, and that the Criteria range covers the criteria header and names. Choose Filter the list, in-place to hide other rows, or Copy to another location to extract matching records. Advanced Filter is a desktop feature; Excel for the web does not offer the same Advanced Filter dialog. The Data tab and dialog arrangement can differ on Mac, so use the corresponding Advanced Filter command if it is available in your desktop edition.
Unlike a FILTER formula, an Advanced Filter result does not automatically refresh just because you edit the criteria list. Run the command again after changing the source or criteria. Be especially careful with the criteria header: it must match the source field name, and an incorrect or missing header can produce unexpected results.
Choose the method that fits your task
- Choose FILTER with COUNTIF for a live, separate result that should recalculate as either table changes. It is the most straightforward choice for many Microsoft 365 users.
- Choose FILTER with XMATCH if you prefer an explicit exact-match lookup or need to avoid COUNTIF wildcard interpretation.
- Choose a helper column when you want to filter the original table, inspect each row’s inclusion status, or use standard table filters alongside the list match.
- Choose Advanced Filter for a one-time extraction or when a formula-based spill is not suitable. Expect to run it again when criteria change.
The standard filter dropdown is another option for a short, stable set of values: open the column’s filter arrow and tick the desired entries. It is quick, but the selections are made manually and do not automatically stay synchronized with a separate list. If the list is long or frequently edited, a formula or helper column is less error-prone.
Common problems and how to fix them
- The formula returns “No matches.” Check spelling, leading or trailing spaces, and whether numbers are stored as text in one place but as numbers in another. Excel’s matching functions are generally not case-sensitive, so differences in capitalization alone usually do not explain a missing match.
- The result shows #SPILL!. Clear any occupied cells in the output area, remove merged cells blocking the result, and place the formula outside an Excel Table. A spilled result cannot overwrite existing content.
- Names containing an asterisk or question mark match too much. COUNTIF interprets these as wildcard characters. Use the XMATCH version for literal exact matching, or carefully escape wildcard characters when using criteria-based functions.
- Repeated values in the list do not create repeated output rows. The formula tests whether a source value has at least one match. It returns each qualifying source row once, even if the name appears several times in MatchList. If the source table itself has duplicate records, each qualifying source record remains in the result.
- New list entries are not being considered. If you used a fixed address such as H2:H10, entries outside that range are excluded. A MatchList table reference expands as rows are added within the table; alternatively, update the fixed range.
Also decide how blank source values should behave. The main FILTER formula above deliberately excludes blank customer cells. If blanks are meaningful values you intend to match, remove the blank-exclusion condition and check that the list contains the kind of blank your data uses. A genuinely empty cell and a formula returning an empty string can behave differently in some comparisons.
Frequently Asked Questions
Can I filter an Excel table by multiple values at once?
Yes. Put each allowed value in its own row in the list table. The COUNTIF and XMATCH formulas treat those entries as alternatives and return a source row when its selected field matches any list entry.
Does FILTER change or delete the original rows?
No. FILTER displays a calculated result in another part of the worksheet; the source table remains unchanged. Use a helper column with the table’s filter arrows if you want to hide nonmatching rows in the original table view.
Can I match against a list on another worksheet?
Yes. Excel Tables can be referenced by name from other worksheets in the same workbook, so a table named MatchList can sit on a separate sheet. Keep the formula’s output area clear on the sheet where you enter it.
Can I make the result show column headers?
FILTER returns matching data rows, not the table header. Add the headers manually above the spill range, or use a separate header row copied from the source table. Avoid placing anything in the cells where the results need to spill.
Will the formula work in older Excel?
FILTER and XMATCH require Microsoft 365 or Excel 2021 or later. In older versions, use the helper-column approach with COUNTIF and filter for TRUE, or use Advanced Filter in desktop Excel.
Related
- Excel Custom Number Format: Hide Zeros Without Deleting Them
- Excel Custom Number Format for Thousands (k)
- Excel Custom Number Format: Add Leading Zeros
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Average Between Two Dates in Excel: Formulas & TipsTo average values whose dates fall between two dates in Excel, use =AVERAGEIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2). This averages numbers in B2:B100…
- How to Concatenate in Excel and Keep Leading ZerosTo concatenate values in Excel and keep leading zeros, format the value with TEXT inside the formula before…
- Excel Not Blank Function: Check Whether a Cell Has DataExcel does not have a function named NOTBLANK. To test whether A2 is not truly empty, use =NOT(ISBLANK(A2));…
- Excel Formula for Cylinder Volume: A Clear ExampleFor a cylinder with its radius in cell A2 and its height in B2, enter =PI()*A2^2*B2 in another…
- Excel Midpoint of Dates: Formula and ExamplesTo find the midpoint between two dates in Excel, put the earlier date in A2 and the later…
- SUMIF and SUMIFS Examples in ExcelIn Excel, use SUMIF to add values that meet one condition and SUMIFS to add values that meet…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.