How to Make XLOOKUP Return All Matches? (5 Easy Methods)

Sharing is caring!

The XLOOKUP function in Excel is one of the most powerful tools for searching data. While it easily finds a single match, many users often ask: “How can I make XLOOKUP return all matches instead of just the first one?”

In this guide, we will walk you through practical methods to achieve this. You’ll learn formulas, supporting functions, and Excel tricks that allow you to extract multiple results from XLOOKUP in a clean and efficient way.

Understanding XLOOKUP Basics

Before focusing on multiple matches, it’s important to understand what XLOOKUP does by default.

• Syntax of XLOOKUP:
  =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
• Default behavior:
  XLOOKUP finds the first matching value in the lookup array and returns the corresponding item from the return array.
• Limitation:
  It does not automatically return all matches when multiple entries exist.

For example:
If you search for “Apple” in a sales table, XLOOKUP will only bring back the first instance of Apple sales, not all rows.

Why You Need All Matches

In many real-world situations, returning only one value is not enough. Common cases include:

• Sales data: Finding all transactions for a specific customer.
• Inventory management: Listing all products from a category.
• Employee records: Returning all roles held by a person.
• Project tracking: Collecting all tasks assigned to a team member.

Getting all matches saves time, ensures accuracy, and eliminates the need for manual filtering.

Method 1: Using FILTER with XLOOKUP Logic

The FILTER function is the best way to simulate XLOOKUP returning multiple values.

Formula:

=FILTER(return_array, lookup_array=lookup_value, "Not Found")

Example:

Imagine you have a table:

To return all sales for Apple:

=FILTER(B2:B6, A2:A6="Apple", "Not Found")

Result: {120;150;180}

Why this works:

FILTER checks all rows where the condition is true and returns every matching value. This makes it act like XLOOKUP for multiple matches.

Method 2: Combining XLOOKUP with SEQUENCE

You can also combine XLOOKUP with SEQUENCE to pull multiple matches by position.

Formula:

=XLOOKUP(lookup_value, lookup_array, return_array, "Not Found", 0, 1+SEQUENCE(n))

• SEQUENCE(n) generates numbers (1, 2, 3, …).
• Each number represents the nth occurrence.
• XLOOKUP then retrieves values in that order.

Example:

=XLOOKUP("Apple", A2:A6, B2:B6, "Not Found", 0, 1+SEQUENCE(3))

This will fetch the first 3 sales values for Apple: {120;150;180}

Note: You must specify how many results (n) you want.

Method 3: Using TEXTJOIN with IF for a Single Cell Result

Sometimes, you may want all matches in one cell instead of separate rows. You can combine TEXTJOIN with an IF array.

Formula:

=TEXTJOIN(", ", TRUE, IF(A2:A6="Apple", B2:B6, ""))

Result: 120, 150, 180

This creates a clean, comma-separated list of all values found.

Method 4: Dynamic Array Formula with INDEX & SMALL

For users who want a step-by-step extraction of multiple matches, INDEX with SMALL is a classic method.

Formula:

=INDEX(return_array, SMALL(IF(lookup_array=lookup_value, ROW(return_array)-MIN(ROW(return_array))+1), ROWS($A$1:A1)))

• Works in older versions of Excel without FILTER.
• Requires pressing Ctrl+Shift+Enter if not using dynamic arrays.
• Each copy of the formula returns the next match.

Method 5: Using Power Query for Complex Data

If you work with large datasets, Power Query is more reliable.

Steps:

1. Load your table into Power Query.
2. Use a filter step to match the lookup value.
3. Load the filtered results back into Excel.

This approach is perfect for advanced users managing thousands of rows.

Comparing Different Methods to Make XLOOKUP Return All Matches

Here’s a comparison table:

Best Practices for Using XLOOKUP with Multiple Matches

1. Use FILTER whenever possible – it’s the cleanest method for modern Excel.
2. Combine with structured references if working in Excel tables.
3. Be clear on output type – choose dynamic arrays vs. single-cell results depending on your needs.
4. Check data consistency – spelling errors or hidden characters may cause missed matches.
5. Test formulas with sample data before applying them to large reports.

Common Errors and Fixes

• #CALC! Error: Happens when FILTER or SEQUENCE cannot find enough matches. Solution: wrap with IFERROR or provide default text.
• #VALUE! Error: Occurs in INDEX-SMALL arrays if ranges are inconsistent. Ensure arrays are the same size.
• Empty Results: Use TRIM or CLEAN functions to remove extra spaces in data.

Real-Life Example: Customer Orders Report

Imagine you run a retail business and want all orders for customer John.

Using FILTER:

=FILTER(B2:C6, A2:A6="John", "Not Found")

Result:

This lets you quickly generate a customer order summary.

When to Avoid Forcing XLOOKUP to Return All Matches

• If you need aggregation (sum, average, count), use functions like SUMIFS, AVERAGEIFS, or COUNTIFS instead.
• If your dataset is too large, FILTER or XLOOKUP with SEQUENCE might slow down performance. Power Query or PivotTables are better.
• If working in older Excel (before Office 365), stick to INDEX-SMALL or helper columns.

Final Thoughts

While XLOOKUP does not directly return multiple results, combining it with functions like FILTER, SEQUENCE, TEXTJOIN, or INDEX-SMALL unlocks that capability.

• Use FILTER for the most straightforward solution.
• Use SEQUENCE with XLOOKUP when you know the number of matches you need.
• Use TEXTJOIN for a compact list in one cell.
• Use INDEX-SMALL or Power Query for compatibility with older Excel or large datasets.

With these methods, you can make XLOOKUP return all matches and manage your data more effectively.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.