Excel Formula to Create a List Based on Criteria

Formulas & functions · Updated November 19, 2026

In Microsoft 365 or Excel 2021, use the FILTER function to create a list from rows that meet a condition. For example, with names in A2:A8 and regions in B2:B8, =FILTER(A2:A8,B2:B8="East","No matches") returns the names whose region is East and displays “No matches” if none qualify.

How the FILTER formula works

FILTER checks an array of values against a matching include condition, then returns the values or rows for which that condition is true. Its basic syntax is =FILTER(array,include,[if_empty]): the array is what you want returned, include is the condition to test, and the optional final argument is what to show when there are no matches. The rows in the array and include range need to line up.

For a concrete example, suppose A2:A8 contains Avery, Blake, Casey, Drew, Ellis, Finley, and Gray, while B2:B8 contains East, West, East, East, North, West, and East. With names in A2:A8 and regions in B2:B8, this formula returns Avery, Casey, Drew, and Gray, in their original order:

=FILTER(A2:A8,B2:B8="East","No matches")

The formula goes in a cell with enough empty space below it for the results. Excel fills the cells automatically; this is called spilling. The formula remains in the top cell, and the cells underneath display its returned list. You do not need to copy the formula down for each result.

Return complete rows instead of one column

To see all the details for matching records, use a multi-column range as the array. Suppose A2:C8 contains names, regions, and sales, with the values shown below. With the same rows in A2:C8 and regions in B2:B8, this formula returns all three columns for every East record:

  • Avery — East — 620
  • Blake — West — 480
  • Casey — East — 350
  • Drew — East — 710
  • Ellis — North — 530
  • Finley — West — 820
  • Gray — East — 540

=FILTER(A2:C8,B2:B8="East","No matches")

The result is four rows: Avery, Casey, Drew, and Gray, each with their region and sales value. The condition range can be one column even when the returned array contains several columns, as long as both ranges cover the same rows. This is useful for extracting whole records from a table without rearranging or copying the source data.

Use more than one condition

You can combine conditions in the include argument. Multiplying condition tests means AND: a row must pass both tests. For example, with names in A2:A8, regions in B2:B8, and sales in C2:C8 as listed above, this returns East records with sales of at least 500:

=FILTER(A2:C8,(B2:B8="East")*(C2:C8>=500),"No matches")

The qualifying rows are Avery (620), Drew (710), and Gray (540). Casey is excluded because the sales figure is 350. In Excel formulas, TRUE and FALSE values from these comparisons are treated as 1 and 0 in the multiplication, so a row is included only when both tests are true.

For OR logic, add the conditions instead. With names in A2:A8 and regions in B2:B8, this returns records from either East or North:

=FILTER(A2:A8,(B2:B8="East")+(B2:B8="North"),"No matches")

The result is Avery, Casey, Drew, Ellis, and Gray. A row that meets either test is included. You can combine several tests this way, but use parentheses around each comparison so Excel evaluates the conditions as intended.

Make the criteria easy to change

Typing a criterion such as East directly into a formula is convenient for a one-off list. If you expect to change the selection, put the criterion in a cell instead. With names in A2:A8, regions in B2:B8, and the chosen region in E2, use:

=FILTER(A2:A8,B2:B8=E2,"No matches")

Enter East, West, or North in E2 to update the results. This approach also works with multiple criteria cells. With the same source columns, E2 holding a region and F2 holding a minimum sales amount, use:

=FILTER(A2:C8,(B2:B8=E2)*(C2:C8>=F2),"No matches")

If E2 contains East and F2 contains 500, the formula returns Avery, Drew, and Gray with their sales details. Using input cells makes a worksheet easier to reuse and reduces the chance of editing the wrong part of a long formula.

Sort results or remove duplicates

FILTER preserves the order of matching rows in the source; it does not automatically sort them. To sort a filtered list alphabetically, wrap the filter in SORT. With names in A2:A8 and regions in B2:B8, this returns East-region names in ascending order:

=SORT(FILTER(A2:A8,B2:B8="East"),1,1)

The first 1 tells SORT to sort by the first column of the returned array, and the final 1 requests ascending order. If you want descending order, use -1 as the final argument. This example assumes there is at least one East match; if a formula must handle an empty result, include an if_empty value in FILTER and consider how that text value should be handled by the sort.

To create a distinct list from filtered values, use UNIQUE around FILTER. With regions in B2:B8 and names in A2:A8, this returns the distinct regions represented by nonblank name rows:

=UNIQUE(FILTER(B2:B8,A2:A8<>"","No matches"))

For the sample records, the distinct values are East, West, and North. UNIQUE removes repeated values; it does not sort them. To sort the distinct result as well, you can combine SORT and UNIQUE, but keep the no-match case in mind when building a formula for data that may change.

Exclude blanks and handle missing matches

A blank in the return column can appear in the filtered output if its row meets the condition. Add a second test to the include argument when blank names should be excluded. With names in A2:A8 and regions in B2:B8, use:

=FILTER(A2:A8,(B2:B8="East")*(A2:A8<>""),"No matches")

This requires both an East region and a nonblank name. You can use the same pattern to exclude blank dates, IDs, or other required fields by testing the relevant column against an empty string. Make sure the criteria ranges cover the same rows as the array.

The optional third argument in FILTER is useful because Excel otherwise returns a calculation error when no rows match. Choose a message such as “No matches” that makes sense in your worksheet. If another formula will use the result, decide whether a text message or an empty result is more appropriate; downstream calculations may treat text differently from numbers or blank cells.

Version support and older Excel alternatives

FILTER, SORT, and UNIQUE are available in Microsoft 365 and Excel 2021 or later. The formulas here are intended for those versions. Excel for Mac also supports these dynamic-array functions in supported editions, and Excel for the web supports them as well. Menu locations and the appearance of the worksheet can differ by platform, but the formula syntax is the same.

In older desktop versions without FILTER, there is no equivalent single dynamic-array formula with the same behavior. One built-in alternative is Advanced Filter, which can copy matching records to another location. In Excel for Windows, set up a criteria range with a heading that exactly matches the source column heading and the condition beneath it, then choose Data > Advanced. Select the list range and criteria range in the dialog and choose Copy to another location if you want a separate result. This method is not a live spilling formula: run it again after the source data changes. Some older versions also use more complex INDEX-based array formulas, but their entry and compatibility requirements vary, so use a method suited to the workbook’s Excel version.

Common problems and practical fixes

  • #SPILL!: One or more cells where the result needs to appear are occupied, merged, or otherwise blocking the spill range. Clear the obstructing cells or move the formula to a larger empty area. A dynamic-array formula cannot spill from inside an Excel table, so place it outside the table.
  • Unexpectedly missing results: Check for extra spaces, inconsistent spellings, or values stored in different formats. A criterion of East will not match a cell containing East followed by an extra space. For numeric comparisons, confirm the values are numbers rather than text that only looks numeric.
  • Wrong rows or a size error: Confirm that the include range has the same number of rows as the array. If the returned range is A2:C20, a condition range covering B2:B19 does not line up.
  • Formula separators: The examples use commas, as requested for standard English-language Excel syntax. Some regional Excel settings use semicolons as argument separators; if Excel rejects a formula with commas, check the list-separator setting for that installation.
  • Results do not update as expected: Check that calculation is set to automatic under Formulas > Calculation Options. Also verify that the source ranges include newly added rows; using an Excel Table as a structured data source can help keep expanding data together, though the formula still needs to be placed where it can spill.

Frequently Asked Questions

Can Excel make a list based on two criteria?
Yes. Multiply the condition tests inside FILTER for AND logic, such as matching a region and meeting a minimum amount. Add the tests for OR logic, where either condition can qualify a row.

Does FILTER copy the matching values?
It displays a dynamic result in the worksheet rather than creating a separate static copy. When the source data or criteria change, Excel recalculates the result. To keep an unchanging snapshot, copy the results and use Paste Special > Values.

Why does my FILTER formula show #SPILL!?
Excel needs a clear spill range beneath or beside the formula. Remove anything occupying the expected result cells, unmerge any cells in that area, or move the formula. A formula that returns multiple rows cannot spill inside an Excel table.

Can I use FILTER to return rows that do not meet a condition?
Yes. Change the comparison in the include argument. For example, use a not-equal comparison to select rows whose region is not East; consider how blank region cells should be treated before using the result.

Does FILTER work in older Excel versions?
Not in versions that lack dynamic-array functions, such as many Excel 2019 and earlier installations. Use Advanced Filter or a version-compatible formula approach instead. Check the exact Excel version before sharing a workbook that relies on FILTER.

Can I put the formula inside an Excel Table?
A formula that returns multiple results cannot spill within a table. Put the FILTER formula in a regular worksheet cell outside the table, and use the table as its source if appropriate.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.