How to How to Use XLOOKUP to Return Multiple Values in Excel in Excel - Practical Guide

Learn how to use XLOOKUP to return multiple values from a dataset in Excel. This advanced technique allows you to retrieve all matching results from a lookup column, not just the first match. Master dynamic arrays and expand functions to create powerful multi-value searches that save time on manual data filtering and consolidation tasks.

Why This Matters

Returning multiple values from lookups is essential for data analysts and business users who need to consolidate data across large datasets without manual copying or filtering.

Prerequisites

•Understanding of basic XLOOKUP function syntax
•Familiarity with Excel dynamic arrays
•Knowledge of data structure and lookup columns

Step-by-Step Instructions

Open Your Data Workbook

Open the Excel file containing your source data with lookup columns and values to return. Identify the lookup column and the column(s) containing the values you want to retrieve.

Create a Helper Column with FILTER

In an empty column, use =FILTER(return_array, lookup_array=criteria) to filter all matching rows first. This prepares your data for multi-value extraction before applying XLOOKUP.

Combine XLOOKUP with Dynamic Array

Enter the formula =XLOOKUP(lookup_value, lookup_array, return_array, , 0) where return_array contains all potential matches. Excel will automatically expand to show all matching results vertically.

Use UNIQUE Function to Remove Duplicates

Wrap your XLOOKUP in UNIQUE: =UNIQUE(XLOOKUP(lookup_value, lookup_array, return_array)) to eliminate duplicate values from results if needed.

Test and Validate Results

Press Enter and verify the spill range shows all matching values correctly. Check for errors (#N/A) and adjust your lookup criteria or array ranges if needed.

Alternative Methods

XLOOKUP with FILTER Combination

Use =FILTER(return_range, (lookup_range=criteria)) to return all matching rows at once without needing separate XLOOKUP calls for each value.

INDEX/MATCH with SMALL and IF

Combine INDEX, MATCH, SMALL, and IF in array formulas to return multiple values; this pre-dynamic array method works in older Excel versions but requires Ctrl+Shift+Enter.

TEXTJOIN with Conditional Logic

Use =TEXTJOIN(",", TRUE, IF(lookup_array=criteria, return_array)) to return all matches as a single comma-separated string in one cell.

Tips & Tricks

✓Always ensure your return_array is larger than your lookup_array to capture all possible matches.
✓Use Ctrl+Shift+Enter for array formulas in Excel versions before 365 to enable proper multi-value returns.
✓Name your ranges (Data > Define Name) to make formulas more readable and easier to maintain.
✓Test your formula with a small dataset first before applying it to large tables.

Pro Tips

★Chain XLOOKUP with SORT to return multiple values in a specific order: =SORT(XLOOKUP(...), 1, 1).
★Use XLOOKUP with -1 parameter to find the exact last match when duplicates exist.
★Combine with UNIQUE to eliminate duplicates and maintain data integrity in consolidation tasks.
★Leverage the [if_not_found] parameter to replace #N/A errors with custom text like 'No Match'.

Troubleshooting

Formula returns blank cells or #N/A in spill range

Check that your return_array extends far enough to contain all matching values. Expand the array reference or use FILTER to pre-select only valid data before passing to XLOOKUP.

Only first match appears instead of all matches

Verify you're using a dynamic array formula (Excel 365+). In older versions, wrap the formula in an array function or use INDEX/MATCH with SMALL instead.

Duplicate values appearing in results

Wrap your XLOOKUP formula with UNIQUE function: =UNIQUE(XLOOKUP(...)) to automatically remove duplicates from the returned results.

Performance slowdown with large datasets

Limit your return_array size, use structured table references, or break data into smaller lookup ranges to improve calculation speed.

Related Excel Formulas

FILTER VLOOKUP INDEX MATCH UNIQUE SORT TEXTJOIN

Frequently Asked Questions

What's the difference between XLOOKUP and VLOOKUP for multiple values?

XLOOKUP is superior because it works in any column direction, supports dynamic arrays for automatic multiple-value expansion, and handles errors gracefully with the [if_not_found] parameter. VLOOKUP only returns one value per cell.

Can XLOOKUP work in Excel 2016 or 2019?

No, XLOOKUP is exclusive to Excel 365 and Excel 2021+. Users of older versions should use INDEX/MATCH with SMALL and IF array formulas or FILTER function alternatives.

How do I return multiple values without using dynamic arrays?

Use TEXTJOIN with IF to concatenate results, or create a helper column with COUNTIF and INDEX/MATCH to retrieve each match individually, then stack results vertically.

What happens if my lookup criteria matches nothing?

By default, XLOOKUP returns #N/A error. Use the optional [if_not_found] parameter: =XLOOKUP(lookup_value, lookup_array, return_array, "No Match") to display custom text instead.

Can I combine XLOOKUP with sorting and filtering?

Yes, wrap XLOOKUP in SORT or FILTER functions: =SORT(XLOOKUP(...), 1, 1) to sort results, or =FILTER(XLOOKUP(...), criteria) to apply additional filtering logic.