Yes. In Excel for Microsoft 365, you can use XLOOKUP to find a value and FILTER to return every row that matches it. With order data in A2:D7, a lookup list in F2:G5, and a name to search in I2, enter =FILTER(A2:D7,C2:C7=XLOOKUP(I2,F2:F5,G2:G5,"Not found"),"No matching rows"). If I2 contains Omar Diaz and the lookup list maps Omar to West, the formula returns all order rows for the West region.
What FILTER and XLOOKUP each do
FILTER and XLOOKUP solve related but different problems. XLOOKUP searches one range for a value and returns a corresponding value from another range. FILTER tests a range against a condition and returns all rows or columns that meet that condition. Combining them lets the lookup result become the condition for a broader list of results.
This differs from Excel’s ordinary column filter, which you use to temporarily show or hide rows in a worksheet. The FILTER function creates a formula result in cells, and that result updates when its source data or inputs change. It does not hide the original rows. A useful way to think about the combination is: “Look up the region for this person, then return every record from that region.”
XLOOKUP returns one match by default, while FILTER can return many. That distinction matters when a region, category, or account is shared by multiple records. If the goal is to return every matching transaction, FILTER is usually the part that must produce the final result.
Set up the example workbook
Use this sample layout to follow the combined formula. In A1:D7, put the order records, with headers in row 1: Order ID in column A, Customer in B, Region in C, and Units in D. The six data rows are 1001, Ava Chen, East, 240; 1002, Omar Diaz, West, 180; 1003, Ava Chen, East, 95; 1004, Priya Shah, North, 310; 1005, Leo Kim, West, 125; and 1006, Mina Roy, South, 220.
In F1:G5, create a small lookup list with Person and Region as headers: Ava Chen/East, Omar Diaz/West, Priya Shah/North, and Mina Roy/South. Enter a person’s name in I2, such as Omar Diaz. The lookup list translates the selected person into a region; the order list then supplies every order in that region.
The layout is intentionally split into two ranges because a lookup table often stores information that is not repeated on every transaction row. In a real workbook, the lookup list might map a product code to a product category, an employee to a department, or an account to a territory. Keep the search values and return values in corresponding rows so each lookup key points to the intended result.
Combine the functions in one formula
With the sample layout above, put the formula in an empty cell outside the source data, such as K2. It looks up the region for the name in I2, compares that region with each value in C2:C7, and returns the matching rows from A2:D7:
With order records in A2:D7, person-to-region mappings in F2:G5, and the selected person in I2, enter =FILTER(A2:D7,C2:C7=XLOOKUP(I2,F2:F5,G2:G5,"Not found"),"No matching rows"). For Omar Diaz, XLOOKUP returns West and FILTER returns order 1002 for Omar Diaz and order 1005 for Leo Kim. Both records are included because both have West in column C.
The formula’s parts have distinct jobs:
XLOOKUP(I2,F2:F5,G2:G5,"Not found")searches for the selected name in F2:F5 and returns the corresponding region from G2:G5. The fourth argument supplies a message if the name is absent.C2:C7=...compares each order’s region with the looked-up region. The comparison produces a series of TRUE and FALSE results.FILTER(A2:D7,...,"No matching rows")returns the rows whose comparison is TRUE. Its third argument provides a readable result if no rows meet the condition.
XLOOKUP uses an exact match by default, which suits this kind of name or code lookup. The ranges also need to line up: A2:D7 contains six data rows, and C2:C7 contains the six corresponding region values. Including headers in one range but not the other can produce incorrect results or an error.
Filter by more than one condition
You do not always need XLOOKUP to set the filter condition. FILTER can combine conditions directly, while XLOOKUP is useful when one of those conditions must first be retrieved from a separate lookup list. For example, you might want orders for one customer above a minimum unit count.
With order records in A2:D7, the customer name in I2 set to Ava Chen, and a minimum units value of 200 in J2, use =FILTER(A2:D7,(B2:B7=I2)*(D2:D7>=J2),"No matching rows"). The multiplication sign combines the TRUE/FALSE tests as an AND condition: both the customer test and the minimum-units test must be true. This returns order 1001, with 240 units; Ava’s 95-unit order does not qualify.
For an OR condition, add the TRUE/FALSE tests instead of multiplying them. The comparison arrays must still cover the same rows as the data being filtered. If a condition depends on a separate mapping, you can put an XLOOKUP inside one comparison, as in the main example, or calculate the lookup result in a helper cell first. A helper cell can make a long formula easier to inspect.
When to use XLOOKUP alone
If you need only one corresponding result, XLOOKUP may be enough. With the sample order records in A2:D7 and the customer name Ava Chen in I2, this formula returns the Units value from the first matching row:
With customer names in B2:B7, units in D2:D7, and the search name in I2, enter =XLOOKUP(I2,B2:B7,D2:D7,"Not found"). It returns 240 because the first Ava Chen record has 240 units. It does not also return Ava’s second order. Use FILTER when you need all matching records, rather than just the first match.
XLOOKUP can search from the bottom upward if its search mode is set accordingly, but that still returns one result, not every duplicate. Conversely, FILTER does not inherently look up a related value in a separate table. Knowing which function handles the search and which handles the multiple-row result helps prevent a common misconception: putting XLOOKUP around a range does not turn it into an all-matches lookup.
Dynamic results and where to put the formula
FILTER returns a dynamic array. Excel places the first result in the formula cell and spills additional rows and columns into neighboring empty cells. You do not copy the formula down to make the output appear. If I2 changes from Omar Diaz to Ava Chen in the combined example, the region becomes East and the matching order rows update automatically.
Leave enough clear space below and to the right of the formula cell for the output. Text, values, merged cells, or other obstructions in the spill area can cause a #SPILL! error. Move or clear the blocking content, or place the formula somewhere with enough room. A spilled result cannot expand inside an Excel table, so enter the formula in a normal worksheet area outside the table. The source data itself can be formatted as a table.
Dynamic array formulas recalculate when relevant inputs change, but the displayed result depends on the source ranges included in the formula. Fixed ranges such as A2:D7 do not automatically extend to new rows added below row 7. For a growing dataset, consider using an Excel table as the source and make sure the formula refers to the table’s data columns, or update the ranges as the data expands.
Common errors and how to fix them
- #N/A: XLOOKUP could not find the search value if its not-found argument is omitted. Check spelling, extra spaces, and whether the lookup value is in the specified search range. The main example supplies “Not found” to handle this case.
- No matching rows: FILTER found no TRUE results. Confirm that the lookup returned the expected value and that the data uses consistent labels. For instance, “West” and “West ” with a trailing space may not compare as equal.
- #SPILL!: One or more destination cells are occupied or otherwise prevent the array from expanding. Clear the spill area or move the formula.
- #VALUE! or unexpected results: Check that the FILTER data range and its include range have compatible dimensions. A six-row data range should be paired with a six-row condition range.
- Only one record appears: Check whether the formula uses XLOOKUP as the final function. XLOOKUP returns a single match; use FILTER around the data range to return all records meeting the condition.
Duplicate names in the lookup list deserve special attention. XLOOKUP returns the first matching lookup entry, so if the same person appears with different regions, the combined formula uses the first region it finds. Make the lookup key unique, or decide explicitly which record should control the result. Names can also be ambiguous; a unique employee ID or product code is generally a safer lookup key.
Excel versions and platform notes
XLOOKUP and FILTER are available in Microsoft 365 and Excel 2021 or later. They are also available in current Excel for the web. Older releases such as Excel 2019 and Excel 2016 do not support these functions, so a workbook using them may not calculate correctly when opened there. If coworkers use older Excel, confirm compatibility before building a workflow around dynamic arrays.
The formulas and comma separators shown here apply to Excel for Microsoft 365 on Windows. Excel for Mac supports these functions in compatible versions as well, and the formula syntax is the same; ribbon locations and some interface details can differ. Regional settings may require semicolons rather than commas as formula argument separators. If Excel rejects a formula despite the function names being recognized, check the list separator configured for that installation.
Frequently Asked Questions
Can XLOOKUP return multiple matching rows?
XLOOKUP returns one matching result, typically the first match. To return every row that meets a condition, use FILTER. You can place an XLOOKUP inside FILTER when the condition depends on a value from another list.
Can I use FILTER with a value returned by XLOOKUP?
Yes. Use the XLOOKUP result in FILTER’s include condition, as in the main example. FILTER then compares that result with the relevant data column and returns all rows that match.
Why does my FILTER and XLOOKUP formula return “No matching rows”?
That message is the formula’s chosen result when FILTER finds no matches. Check that XLOOKUP returns the intended value, that the lookup key is present, and that the comparison values have no spelling or spacing differences.
Can I use these functions with Excel tables?
Yes. Tables can be a convenient source of expanding data, but enter the spilled FILTER formula in a worksheet area outside the table. A dynamic array cannot spill into the cells of an Excel table.
What if my Excel version does not recognize FILTER or XLOOKUP?
The functions require Excel 2021 or Microsoft 365, or a compatible newer release. In older versions, use another supported approach, such as a helper column with worksheet filters or a lookup method available in that version; the exact alternative depends on whether you need one match or all matching rows.
Related
- Excel Data Validation List Without a Cell Reference?
- Excel Data Validation List: Can You Select Multiple?
- Excel Data Validation List: Remove Duplicates
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Edit Filter Options in Excel for Better ResultsTo edit filter options in Excel, select a cell in your data, choose Data > Filter, then open…
- Excel Date Math: Formulas, Serial Numbers, and ExamplesExcel date math works by treating dates as numbers: subtract one date from another to count elapsed days,…
- COUNTIF Excel Range: What to Select and How It WorksIn Excel, the range in COUNTIF is the group of cells the function checks; the basic form is…
- CONCAT in Excel: Add a Space Between Text ValuesTo add a space when joining text in Excel, include a space in quotation marks as an argument:…
- Excel Concatenate String Examples: Formulas That WorkTo concatenate strings in Excel, join cell references and text with the ampersand operator (&), or use CONCAT…
- Excel Test Cell Value: Formulas for Common ChecksTo test whether a cell contains a particular value in Excel, use an IF formula such as =IF(A2="Paid","Match","No…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.