XLOOKUP Concatenate Multiple Matches in Excel

Formulas & functions · Updated November 22, 2026

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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.