How to Lookup Multiple Values in Excel with XLOOKUP (No Filter Needed!)
Stop settling for just the first match! Learn how to use the XLOOKUP function to lookup and return multiple values in Excel vertically or horizontally. Step-by-step guide included.
Are you stuck in a loop of using Ctrl+F and copying values one by one? Or maybe you’ve tried VLOOKUP and XLOOKUP, only to be frustrated when they return just the first matching result.
If you need to find all sales for a specific rep, all products in a category, or every instance of a value, you need a better way. Manually searching isn't it.
The good news? You don't need complicated array formulas or the FILTER function (though it's great!). You can do this directly with XLOOKUP.
In this guide, I’ll show you the precise formula to lookup and extract multiple values based on a single criteria.
The Problem: Traditional Lookups Fall Short
Let’s say you have a standard dataset. A normal XLOOKUP formula like this:=XLOOKUP(F2, A2:A10, B2:B10)
...will only ever give you the first match it finds. But what if your data looks like this?
Sample+Data+Table+with+Multiple+Matches
We need a formula that finds all of "Anna's" sales, not just her first one.
The Solution: Unleashing XLOOKUP's Full Power
The secret lies in leveraging how modern Excel handles arrays. With the current version of Excel (Office 365/Microsoft 365), a single formula can "spill" its results into multiple cells automatically.
Here is the magic formula to return multiple matches vertically in a column:
=XLOOKUP(F2, A2:A10, B2:B10, "Not Found", 2, 1)
Let’s break down why this works, piece by piece:
F2: This is our lookup value (e.g., "Anna").
A2:A10: The range where we are searching for the lookup value.
B2:B10: The range from which we want to return a value.
"Not Found": The friendly text to display if no matches are found.
2: The
[match_mode].2is a wildcard match, but the key is the next argument.1: The
[search_mode]. This tells XLOOKUP to search from the first value to the last. This setup, combined with Excel's new engine, allows it to return an array of all matching results.
When you press Enter, watch as Excel automatically spills all the results into the cells below.
How to Return Results Horizontally in a Row
Need the data in a row for a summary report? It’s just as easy. Simply wrap our powerful XLOOKUP formula inside the TRANSPOSE function.
=TRANSPOSE(XLOOKUP(F2, A2:A10, B2:B10, "Not Found", 2, 1))
This will take the vertical array of results and lay it out horizontally for you.
Handling Errors and Making It Bulletproof
You might encounter a #SPILL! error if there are cells blocking where the results need to appear. Simply clear the cells below or to the right of your formula and it will work.
The "Not Found" part of our formula already handles #N/A errors gracefully. If the value isn’t in the list, it will display your custom text instead of an ugly error.
See It In Action!
While the formulas above are powerful, sometimes seeing the process makes all the difference. I've put together a detailed video tutorial that walks you through each step visually, from the common problem to the advanced solution.
👉 Watch the full tutorial here: https://youtu.be/zor0T5FUvtw
In the video, you'll see:
A live demo of the problem.
A step-by-step build of the formula.
How to handle both vertical and horizontal spills.
Pro tips for troubleshooting common errors.
Conclusion
You no longer have to manually comb through data or settle for incomplete results. By mastering this technique, you can make your Excel workbooks more dynamic and powerful than ever before. The XLOOKUP function is truly a game-changer.
Ready to master more Excel tricks? Subscribe to our channel for weekly tips that will save you time and boost your productivity!
#Excel #XLOOKUP #ExcelTips #DataAnalysis #MicrosoftExcel #Productivity #Tutorial #LearnExcel #Spreadsheets
