Excel Formula to Return Multiple Lookup Results

Formulas & functions · Updated November 19, 2026

To return every row that matches a lookup value in Microsoft 365 Excel, use the FILTER function. For example, with lookup keys in A2:A8, the rows to return in A2:C8, and the requested key in E2, enter =FILTER(A2:C8,A2:A8=E2,"No matches"); Excel spills all matching rows into nearby cells.

How a multiple-result lookup works

Traditional lookup formulas such as VLOOKUP and XLOOKUP are designed to return a result for one match: usually the first matching value they find. That behavior is useful when each key is unique, but it is not enough when a key appears repeatedly and you want every associated record. FILTER solves that different problem by testing each row against a condition and returning the rows that pass.

For a concrete example, imagine a worksheet with Region in column A, Product in column B, and Units in column C. Rows 2 through 8 contain these records: North/Cedar/12, South/Maple/8, North/Birch/15, North/Cedar/7, South/Birch/11, North/Maple/20, and South/Cedar/5. Put the region to search for in E2. With that layout, the formula in the opening paragraph returns four rows when E2 contains North: Cedar/12, Birch/15, Cedar/7, and Maple/20. It preserves the source order and includes all three requested columns.

The first argument tells FILTER what to return; the second supplies a test that is evaluated for each row; the optional third argument supplies a message when nothing matches. A lookup value does not have to be unique. If there are no matching rows, the example displays No matches instead of an empty-result error.

Set up the source range and lookup cell

Keep the lookup column and the rows you want returned aligned. In the example, the lookup test covers A2:A8 and the return range covers A2:C8, so both cover exactly seven data rows. If those ranges start or end on different rows, Excel may return an error or associate the wrong tests with records. Exclude column headings from the formula ranges unless you deliberately want the headings treated as data.

Place the formula in a blank area with enough room for its results. You can put it on the same sheet outside the source table or on another worksheet. Enter the lookup term in E2, then edit that cell to see the returned rows update. If you convert the source data to an Excel Table, adding records to the table can make the source easier to maintain; structured references are another option, but ordinary cell references are often clearer when learning the pattern.

For a one-column result, use only that column as the return range. With Region in A2:A8, Product in B2:B8, and the requested region in E2, this formula returns just the matching product names: =FILTER(B2:B8,A2:A8=E2,"No matches"). For North, the results are Cedar, Birch, Cedar, and Maple. Repeated product names remain repeated because FILTER returns records, not a distinct list.

Return selected columns or sort the matches

The return range controls what appears in the result. If the source has many fields but you only need product names and units, you can choose a return range that contains just those columns only when they are adjacent. For nonadjacent columns, a convenient Microsoft 365 approach is to first filter a contiguous range and then select or rearrange columns with newer array functions, or arrange a small helper range next to the source. Keeping the formula simple is often easier to audit than building a complicated expression.

To sort matching rows by Units from largest to smallest, with Region in A2:A8, Product in B2:B8, Units in C2:C8, and the lookup region in E2, use =SORT(FILTER(A2:C8,A2:A8=E2),3,-1). Assuming E2 contains North, this returns North/Maple/20, North/Birch/15, North/Cedar/12, then North/Cedar/7. The 3 tells SORT to sort by the third column of the returned range, and -1 requests descending order. This version assumes there is at least one match; add an if-empty message to FILTER if a no-match result must be handled, but remember that SORT expects a sortable array.

Sorting is optional. FILTER itself returns matches in the order they occur in the source data. If the source is already ordered the way readers need, there is no need to add a sorting function.

Match more than one condition

You can combine tests when a lookup should depend on multiple fields. Multiplying conditions acts like AND: every condition must be true for a row to be included. With Region in A2:A8, Product in B2:B8, Units in C2:C8, the selected region in E2, and a minimum unit count in F2, enter this formula to return rows for that region with at least the specified units: =FILTER(A2:C8,(A2:A8=E2)*(C2:C8>=F2),"No matches"). If E2 is North and F2 is 10, the result is Cedar/12, Birch/15, and Maple/20.

Adding conditions with multiplication is a common source of confusion because the asterisk is functioning as a logical AND in this context, not multiplying the displayed values. For OR logic, add conditions instead. With the same source layout, this returns records where the region is North or the product is Cedar: =FILTER(A2:C8,(A2:A8="North")+(B2:B8="Cedar"),"No matches"). The North/Cedar records appear once each, not twice: each source row is evaluated once and included if its combined test is true.

Comparisons can use values in worksheet cells, as in the AND example, rather than fixed text typed into the formula. That makes the lookup reusable: change E2 or F2 and the results recalculate. For text criteria, normal equality tests do not distinguish uppercase from lowercase in typical Excel comparisons. If exact case matters, a different approach using the EXACT function is needed.

Dynamic arrays and spill behavior

FILTER returns a dynamic array. Instead of copying a formula down one row at a time, Excel places the returned values in a rectangular spill range beginning at the formula cell. When the source data or lookup cell changes, Excel recalculates the size and contents of that range. Do not type into cells where the results need to spill, because occupied cells block the output.

A blocked output usually produces a #SPILL! error. Clear or move the content in the intended spill area, and check for merged cells or other obstructions. A result may also be wider or taller than expected if the return range includes more columns or matches more records than anticipated. The formula cell is the only cell containing the formula; the other visible results are generated by the spill.

Dynamic-array formulas generally cannot spill from inside an Excel Table. Put the formula outside the table, or use a normal worksheet range for the output. The source itself can be a table, but the destination needs open worksheet cells for the returned array. This distinction is useful when a workbook’s input records are already formatted as a table.

Common errors and practical fixes

  • #SPILL! Check that every cell needed for the output is empty and not merged. Move the formula or clear the obstructing cells.
  • #CALC! FILTER can return this when there are no matches and no if-empty argument was supplied. Add a message such as the third argument in the examples.
  • Unexpectedly missing matches: Check for leading or trailing spaces, inconsistent spelling, and whether the lookup value is text while source values are stored as numbers, or vice versa. A number formatted to look like text can still behave differently in a comparison.
  • Wrong records or a reference error: Confirm that the include range has the same number of rows as the return range. For a multi-condition formula, verify that every condition range is aligned with the same source rows.
  • Formula appears as text: Check that the cell is not formatted as Text and that the formula starts with an equals sign. Re-enter it after changing the cell format to General if needed.

Excel’s list separator can depend on the computer’s regional settings. The formulas here use commas, as in standard English-language examples. If Excel rejects a formula despite correct references, the local installation may expect semicolons between arguments; use the separator Excel recognizes.

FILTER compared with other lookup methods

Use FILTER when the goal is to return all matching rows or values and the Excel version supports dynamic arrays. Use XLOOKUP when you need one corresponding result, such as a single price for a unique product code. XLOOKUP returns one match by default; it is not a substitute for FILTER when every repeated match must be listed. VLOOKUP also returns one matching result and has additional layout limitations, including requiring its lookup column to be the first column in its table array.

For older Excel versions without FILTER, a standard AutoFilter can display matching records without creating a separate formula result. Select the data and use Data > Filter, then choose the desired value from the lookup-column dropdown. This is a practical manual alternative, but it filters the original list in place rather than producing a separate spill range that updates from a lookup cell.

Power Query is another option for recurring data-cleaning or reporting workflows, especially when records are imported from external sources. It can filter rows and load a resulting table, but it involves a query refresh process rather than a cell formula that recalculates immediately. For a quick interactive lookup on a modern worksheet, FILTER is usually the most direct choice.

Version availability and compatibility

FILTER is available in Microsoft 365 and Excel 2021 or later, including the applicable Excel for the web versions. A workbook containing a dynamic-array formula can be opened in an older Excel release, but a version that does not support the function may show an unsupported-function error rather than a live spilled result. If coworkers use older versions, consider a filtered table, Power Query, or a compatibility-specific formula approach instead of assuming everyone can use FILTER.

In Excel for the web, the basic formula and spill behavior are similar. The exact interface may differ from the Windows desktop app, but the formula can be entered in a worksheet cell. On Mac, Microsoft 365 Excel also supports the function; the formula syntax is the same, although menu names and keyboard shortcuts can differ. No special array-entry keystroke is required for FILTER in supported versions.

Frequently Asked Questions

Can XLOOKUP return multiple matching results?
XLOOKUP is intended to return a single matching result by default. For all matching rows or values, use FILTER in a supported Excel version. If you only need the first matching record, XLOOKUP is often simpler.

How do I return multiple columns for each match?
Set FILTER’s first argument to the full rectangular range of columns you want returned, while keeping the include condition aligned to the same rows. For example, a return range of A2:C8 can produce three columns for every matching row.

Can I return matches from another worksheet?
Yes. Reference the source worksheet in both the return and condition ranges, while keeping the lookup cell reference appropriate to the formula’s location. For example, a source on a sheet named Data can be referenced as Data!A2:C8 and Data!A2:A8.

Why does my formula show #SPILL!?
Excel cannot place the full result because one or more destination cells are occupied or otherwise unavailable. Clear the blocked cells, unmerge cells in the output area, or move the formula to a location with enough open space.

Can FILTER return multiple matches in older Excel?
FILTER requires Microsoft 365 or Excel 2021 and later. In older versions, use Data > Filter to display records, or consider a Power Query workflow. Older formula-based solutions are possible, but they are more complex and depend on the Excel version and whether the result must update automatically.

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.