Yes, you can concatenate multiple matches in Excel by combining XLOOKUP with other functions—specifically, you cannot make XLOOKUP return multiple values directly, but you can use it inside a formula that spills or joins results. The most reliable approach is to use XLOOKUP with the FILTER function (available in Excel 2021 and Microsoft 365) to return all matching values, then join them with TEXTJOIN. For example, with lookup values in A2:A10 and return values in B2:B10, the formula =TEXTJOIN(", ", TRUE, XLOOKUP(F2, A2:A10, B2:B10)) will return only the first match, not all. To get all matches, use =TEXTJOIN(", ", TRUE, FILTER(B2:B10, A2:A10=F2)), which spills all matching B values and joins them. This article explains why XLOOKUP alone can’t do this, how to build the correct formula, and common pitfalls.
Why XLOOKUP Only Returns One Match
XLOOKUP is designed to return a single value (or a single row/column if you use the return_array argument). When you use XLOOKUP(lookup_value, lookup_array, return_array), it finds the first exact match by default and returns the corresponding value from the return array. If there are multiple matches, it ignores the rest. This is different from older functions like VLOOKUP, but the behavior is the same: one match per lookup. To concatenate multiple matches, you need a function that can return an array of all matches—FILTER is the modern tool for that.
The Modern Solution: FILTER + TEXTJOIN
The cleanest way to concatenate multiple matches is to use FILTER to extract all matching values and TEXTJOIN to combine them into a single string. Here’s a step-by-step example:
- Assume your data is: Column A has product names (A2:A10), Column B has sales amounts (B2:B10).
- Put the lookup value (e.g., “Widget”) in cell D2.
- In cell E2, enter:
=TEXTJOIN(", ", TRUE, FILTER(B2:B10, A2:A10=D2))
This formula filters B2:B10 where A2:A10 equals D2, returns an array of all matching sales amounts, and TEXTJOIN joins them with a comma and space. The second argument (TRUE) ignores empty cells. If no matches exist, FILTER returns a #CALC! error, which you can handle with IFERROR: =IFERROR(TEXTJOIN(", ", TRUE, FILTER(B2:B10, A2:A10=D2)), "No matches"). This works in Excel 2021 and Microsoft 365. In Excel 2019 or earlier, FILTER isn’t available; you’d need a complex array formula or a helper column.
Using XLOOKUP with a Helper Column
If you prefer to stick with XLOOKUP (for compatibility or habit), you can add a helper column that numbers each occurrence of a lookup value, then use XLOOKUP to retrieve each one sequentially. For example:
- Add a helper column C with formula in C2:
=COUNTIF($A$2:A2, A2)— this gives 1 for the first occurrence, 2 for the second, etc. - In D2, enter the lookup value (e.g., “Widget”).
- In E2, enter:
=TEXTJOIN(", ", TRUE, XLOOKUP(D2, A2:A10, B2:B10, "", 0))— but this still returns only the first match.
To get all matches with XLOOKUP, you’d need to create a dynamic array of occurrence numbers. A better approach is to use XLOOKUP with a concatenated key: create a unique ID by combining the lookup value with the occurrence number. For instance, in D2, put the lookup value, and in E2, use: =TEXTJOIN(", ", TRUE, XLOOKUP(D2&"-"&SEQUENCE(COUNTIF(A2:A10,D2)), A2:A10&"-"&COUNTIF($A$2:A2,A2), B2:B10)). This is complex and error-prone—simpler to use FILTER.
How to Choose Between XLOOKUP and FILTER
Use FILTER when you need to return multiple matches and you’re on Excel 2021 or Microsoft 365. Use XLOOKUP when you need a single match, or when you need to search from the end (using the search_mode argument) or perform a wildcard match. If you’re on an older Excel version, XLOOKUP isn’t available at all; you’d use VLOOKUP or INDEX/MATCH. For concatenation, FILTER is the go-to because it’s designed to spill arrays. XLOOKUP can return an array if you give it a return_array that spans multiple rows, but it will only return the first match’s corresponding row—not all matches.
Common Problems and How to Fix Them
- #CALC! error: FILTER returns this when no matches are found. Wrap with IFERROR to show a custom message.
- Spill range overlaps: If you have data in adjacent cells, the FILTER result may spill into them. Ensure the cells below the formula are empty.
- Text vs. number mismatch: If your lookup value is a number stored as text, use
=TEXTJOIN(...)with a value that matches the data type. Use--to convert text to numbers if needed. - Case sensitivity: FILTER and XLOOKUP are case-insensitive by default. For case-sensitive matching, use EXACT inside FILTER:
=FILTER(B2:B10, EXACT(A2:A10, D2)). - Empty cells in results: TEXTJOIN with TRUE ignores empty cells, but if you want to include them as empty strings, use FALSE.
Alternatives: TEXTJOIN with IF, or Power Query
If you can’t use FILTER (older Excel), you can use an array formula with TEXTJOIN and IF. In Excel 2019, you can enter: =TEXTJOIN(", ", TRUE, IF(A2:A10=D2, B2:B10, "")) as an array formula (press Ctrl+Shift+Enter). This works but requires CSE entry. Another alternative is Power Query (Get & Transform Data) to group rows and concatenate values—a more permanent solution for large datasets. For a one-off report, the FILTER method is simplest.
Frequently Asked Questions
Can XLOOKUP return multiple matches at all? No, XLOOKUP is designed to return a single match. To get multiple matches, you must use FILTER or another array function.
What if I need to concatenate matches from two columns? Use FILTER to return both columns, then combine them: =TEXTJOIN(", ", TRUE, FILTER(A2:A10&" - "&B2:B10, C2:C10=D2)).
Does TEXTJOIN work in Excel 2016? No, TEXTJOIN was introduced in Excel 2019. In Excel 2016, you’d need a VBA user-defined function or a complex formula with CONCATENATE and IF.
How do I handle duplicates in the lookup column? FILTER will return all duplicates. If you want unique matches, wrap with UNIQUE: =TEXTJOIN(", ", TRUE, UNIQUE(FILTER(B2:B10, A2:A10=D2))).
Is there a way to concatenate matches without a helper column? Yes, using FILTER directly, as shown above. No helper column needed.
What happens if I use XLOOKUP with a return array of multiple rows? XLOOKUP will return the entire row that matches, not all matching rows. For example, =XLOOKUP(D2, A2:A10, B2:B10) returns one value; if you use =XLOOKUP(D2, A2:A10, B2:C10), it returns two columns for the first match, but still only one row.
Related
- Excel Timesheet Template for Multiple Projects
- Excel Templates for Time Tracking: Best Free Options
- Profit and Loss Excel Templates: How to Use Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- JavaScript String startsWith() Function: How It WorksYes. JavaScript provides the string method startsWith() to check whether a string begins with a specified sequence of…
- String Functions BigQuery: The Essential GuideThe essential string functions in BigQuery are CONCAT, SUBSTR, REPLACE, UPPER, LOWER, TRIM, SPLIT, REGEXP_EXTRACT, and REGEXP_REPLACE, which…
- How to Find a Middle Name in Excel: Formula and StepsWith full names in A2 and names written in first-middle-last order, enter =IFERROR(INDEX(TEXTSPLIT(TRIM(A2)," "),1,2),"") in B2 to return…
- Filter Excel Data by Text: Steps and Useful OptionsTo filter Excel data by text in Microsoft 365 for Windows, select a cell in your data, choose…
- Average Value in Excel: Formula, Steps, and Practical TipsTo find an average in Excel, use the AVERAGE function. With the numbers 12, 15, 18, 20, and…
- Balance Sheet Equation: Meaning, Formula, and ExamplesThe balance sheet equation is Assets = Liabilities + Equity. It means that everything a business owns or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.