Excel’s XLOOKUP is great for finding a needle in a haystack, but what if you want all the needles? While XLOOKUP stops at the first match, the FILTER function is built for the dynamic array era, allowing you to pull entire lists of data with a single, elegant formula.
Why XLOOKUP isn’t always the hero
XLOOKUP—available in Excel for Microsoft 365, Excel for the web, and one-off versions of the app released in 2021 or later—is the gold standard for data retrieval. It’s significantly easier to use than the INDEX-MATCH combo, and it’s far more flexible than its predecessors, VLOOKUP and HLOOKUP. It’s smarter, too. For example, XLOOKUP can spill multiple columns for a single match—if you look up an employee ID, it can automatically fill the name, department, and start date in one go.
But it has a fundamental limitation: it’s designed to find a single result. When your data contains multiple records for the same criteria, like a list of every sale in the north region or every invoice for a specific client, XLOOKUP isn’t the tool for the job because it stops at the first match.
How the FILTER function changes the game
The FILTER function belongs to a class of modern dynamic array functions, meaning you type the formula once, and the results spill into as many cells as necessary—you don’t need to drag the fill handle down 100 rows.
Related
Everything You Need to Know About Spill in Excel
It’s not worth crying over spilled references.
Even though it can perform complex operations, the FILTER function’s syntax is basic:
=FILTER(array,include,[if_empty])
- array (required) is the range of cells or the table you want to filter.
- include (required) is the criterion that tells Excel what to keep in the filter.
- [if_empty] (optional) is where you specify what Excel should display if no matches are found.
Unlike the standard filter tool found on the Data tab, the FILTER function is live. If you add a new entry, it appears in your results instantly. And unlike the XLOOKUP function, it returns every match it finds, not just the first one.
Example 1: Pulling all sales for a specific region
Suppose you have a master sales log in an Excel table named T_Sales and need to extract every transaction for the north region.
This is where the difference between a lookup and a filter becomes clear. Imagine you first try to solve this using the following XLOOKUP formula:
=XLOOKUP(F2,T_Sales[Region],T_Sales,”No records found”)
You’ll immediately notice a problem: while XLOOKUP is smart enough to spill horizontally, returning the date, salesperson, and amount for a single match, it only finds the first sale, ignoring the other three sales in that region.
To get every sale, use the FILTER function in cell H2 instead:
=FILTER(T_Sales,T_Sales[Region]=F2,”No records found”)
Unlike XLOOKUP, the FILTER function scans the entire Region column, and every time it finds a match for the value in F2, it pulls that entire row into your results area.
Because this is a dynamic array and the formula references an Excel table, if you add a new sale from the north to the dataset, the results will automatically expand to include it. Similarly, if you change cell F2 to “West,” the list updates instantly.
Example 2: Filtering by multiple criteria
In the first example, I showed you how to use FILTER in Excel to pull a list based on a single condition. However, real-world data extraction usually requires more precision.
Let’s say you want to extract all of Miller’s sales in the north region,
Even though XLOOKUP was primarily designed to work with a single criterion, it can also handle complex searches by concatenating values (using the ampersand symbol) or using Boolean logic. However, this flexibility doesn’t change XLOOKUP’s fundamental flaw of returning only one match.
The FILTER function, on the other hand, handles multiple criteria natively—you can tell it to scan your table for rows where condition A and condition B are true—and returns every matching record:
=FILTER(T_Sales,(T_Sales[Salesperson]=F2)*(T_Sales[Region]=F5),”No matches found”)
Why the asterisk?
This looks like a math problem because, to Excel, it is. This method relies on Boolean logic, where the criteria in the formula are evaluated and translated into numerical values: TRUE becomes 1, and FALSE becomes 0. By placing an asterisk (*) between your conditions, you’re telling Excel to multiply them row by row:
Table row
Salesperson = Miller
Region = North
Result
1
Miller (TRUE = 1)
North (TRUE = 1)
1 x 1 = 1 (keep)
2
Smith (FALSE = 0)
South (FALSE = 0)
0 x 0 = 0 (discard)
…
10
Smith (FALSE = 0)
North (TRUE = 1)
0 x 1 = 0 (Discard)
As a result, only the rows that evaluate to 1 (or TRUE) are included in the final spilled result. In fact, you can include as many requirements as you need, such as a specific date range or minimum amount—simply wrap each condition in parentheses and separate them with the asterisk.
While the asterisk is used for AND logic, where both conditions must be met, the plus sign (+) can be used for OR logic, where any one of the conditions can be met to be included in the result.
Related
The Ultimate Glossary of Microsoft Excel Symbols
You don’t truly understand Excel until you know its symbols.
While the FILTER function is a game-changer for data extraction, you shouldn’t abandon XLOOKUP altogether. Both functions deserve a permanent spot in your Excel toolkit, but the trick is knowing which one to grab based on what you’re aiming to do.
If you want to…
Then use…
Because…
Find one specific record
XLOOKUP
It’s built for one-to-one lookups. While FILTER can also return a single result, XLOOKUP’s formula is often faster to write and easier to read.
Extract a list of records
FILTER
It scans the entire table and spills every matching row into a dynamic list.
Find an approximate match
XLOOKUP
It has a built-in match mode for tiered data like tax brackets.
Search by multiple criteria
FILTER
It uses Boolean logic to handle complex AND/OR searches. XLOOKUP can also handle multiple criteria, but FILTER’s syntax is much more intuitive for extracting lists.
Use wildcards (*, ?)
XLOOKUP
It supports wildcards in its syntax for partial text matches.
Build a live report
FILTER
It automatically grows or shrinks as your data source changes.
Once you extract your Excel data using FILTER, you can further refine your reports using the UNIQUE function to remove duplicates from your filtered results. This allows you to generate clean, professional summaries that highlight distinct values without the clutter of repetitive entries, ensuring your final dashboard remains concise and actionable.
OS
Windows, macOS, iPhone, iPad, Android
Free trial
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
