Excel XLOOKUP with CONCATENATE: How to Join

Formulas & functions · Updated November 22, 2026

Yes, you can use XLOOKUP with concatenated values by building the lookup value with the & operator or the CONCAT function, and if your lookup table also stores combined keys, you may need to create a helper column or use XLOOKUP with an array formula. For example, to look up a customer by first and last name in separate columns, you write =XLOOKUP(A2&" "&B2, C:C&" "&D:D, E:E) in Excel 365. This article explains the exact syntax, when you need a helper column, common errors, and alternatives like INDEX/MATCH.

Why Combine XLOOKUP and CONCATENATE?

XLOOKUP normally matches a single lookup value against a single lookup array. But real data often splits a unique identifier into multiple columns—first name and last name, city and state, product code and color. To look up based on two or more criteria, you need to create a combined key. Concatenation joins those pieces into one string, and XLOOKUP then searches for that string. This is a common need when your data lacks a single unique ID column, or when you want to avoid adding a helper column to your source table.

How to Concatenate in Excel

Excel offers several ways to join text:

  • Ampersand operator: =A2&B2 joins the contents of A2 and B2 with no separator. Add a space or delimiter: =A2&" "&B2.
  • CONCAT function: =CONCAT(A2," ",B2)—works in Excel 2016 and later, but does not accept ranges directly (use CONCATENATE for ranges).
  • TEXTJOIN function: =TEXTJOIN(" ",TRUE,A2:B2)—available in Excel 2019 and later, allows a delimiter and ignores empty cells.

For XLOOKUP, the ampersand is usually simplest because it works in all versions and is easy to read.

Basic XLOOKUP with Concatenated Lookup Value

The simplest scenario: your lookup value is in two cells (say A2 and B2), and your lookup table has a column that already contains the combined key (e.g., column C has full names like “John Smith”). Then you just concatenate the two cells:

Example: With first name in A2, last name in B2, full name in C2:C100, and email in D2:D100, use:

=XLOOKUP(A2&" "&B2, C2:C100, D2:D100)

This returns the email for “John Smith” if A2=”John” and B2=”Smith”. The formula works because the lookup value is a single text string.

When Your Lookup Table Has Separate Columns

If the lookup table also stores first and last names in separate columns (e.g., column A = first, column B = last, column C = email), you cannot directly use a single range as the lookup array. You have two options:

  1. Create a helper column: Add a column (e.g., column D) with =A2&" "&B2 and drag down. Then use =XLOOKUP(A2&" "&B2, D2:D100, C2:C100). This is the most reliable and fastest method.
  2. Use an array formula: In Excel 365, you can concatenate the entire columns on the fly: =XLOOKUP(A2&" "&B2, A2:A100&" "&B2:B100, C2:C100). This works because the lookup array is computed as an array of concatenated strings. However, it can be slower on large datasets (over tens of thousands of rows) and may require pressing Ctrl+Shift+Enter in older versions (though Excel 365 handles it automatically).

Both methods return the same result. The helper column is easier to debug and faster for big data.

Common Problems and Solutions

  • Spacing mismatches: If your data has extra spaces (e.g., “John ” vs “John”), the concatenated keys won’t match. Use TRIM on both sides: =XLOOKUP(TRIM(A2)&" "&TRIM(B2), TRIM(C2:C100)&" "&TRIM(D2:D100), E2:E100).
  • Case sensitivity: XLOOKUP is case-insensitive by default, so “john” matches “John”. If you need case-sensitive matching, add the 5th argument: =XLOOKUP(..., 0, -1)? Actually, the 5th argument is match_mode; for case-sensitive you need to use EXACT in an array formula or a helper column with EXACT. Simpler: use XLOOKUP with a wildcard? No—case sensitivity is not a built-in option. Use INDEX/MATCH with EXACT if needed.
  • Numbers stored as text: If one part is a number and the other is text, concatenation may produce “123John” vs “123 John”. Ensure consistent formatting or use TEXT to format numbers: =A2&TEXT(B2,"0").
  • #N/A errors: This means no match found. Check for invisible characters (like line breaks) using CLEAN and TRIM.

Alternatives: INDEX/MATCH and FILTER

If you are using Excel 2019 or earlier, XLOOKUP is not available. Use INDEX/MATCH with concatenation:

Example: With first names in A2:A100, last names in B2:B100, emails in C2:C100, and lookup cells E2 (first) and F2 (last):

=INDEX(C2:C100, MATCH(E2&" "&F2, A2:A100&" "&B2:B100, 0))

This is an array formula, so in Excel 2019 and earlier you must press Ctrl+Shift+Enter. In Excel 365, it works normally. Another alternative is the FILTER function (Excel 365):

=FILTER(C2:C100, (A2:A100=E2)*(B2:B100=F2), "Not found")

This returns all matching emails; if you expect only one, wrap it in @ or use INDEX on the result.

Performance and Best Practices

Concatenating entire columns in an array formula (like A2:A100&" "&B2:B100) forces Excel to compute an array for every row, which can slow down large workbooks. For datasets over 50,000 rows, prefer a helper column. Also, avoid full-column references (e.g., A:A) in array formulas because they process over a million cells. Use a defined range like A2:A100000 if needed. Finally, if your lookup values contain numbers, ensure they are formatted consistently—using TEXT can prevent mismatches.

Frequently Asked Questions

Can I use XLOOKUP with CONCATENATE on two columns without a helper column? Yes, in Excel 365 you can use =XLOOKUP(A2&" "&B2, C2:C100&" "&D2:D100, E2:E100). This works because the lookup array is computed as an array. In older versions, you must use a helper column or array-enter with Ctrl+Shift+Enter.

Why does my XLOOKUP return #N/A even though the values look the same? Often it’s due to extra spaces, non-printing characters, or different data types (text vs number). Use TRIM and CLEAN on both the lookup value and the lookup array. Also check that the concatenation separator exactly matches (e.g., space vs no space).

Is there a way to make XLOOKUP case-sensitive when concatenating? XLOOKUP itself is case-insensitive. To make it case-sensitive, you can use an array formula with EXACT: =XLOOKUP(TRUE, EXACT(A2&" "&B2, C2:C100&" "&D2:D100), E2:E100). This works in Excel 365. Alternatively, use INDEX/MATCH with EXACT.

What is the difference between CONCAT and CONCATENATE? CONCAT is the newer function (Excel 2016+) and accepts a range of cells (e.g., CONCAT(A2:B2)), while CONCATENATE is older and only accepts individual cell references. For XLOOKUP, the ampersand is usually simpler and faster.

Can I use wildcards with XLOOKUP and concatenation? Yes, XLOOKUP supports wildcards if you set the match_mode argument to 2 (wildcard match). For example, =XLOOKUP("*"&A2&"*", C2:C100, D2:D100, , 2). But when concatenating, be careful that the wildcard characters don’t appear in your data.

Does XLOOKUP work with concatenated values in Google Sheets? No, XLOOKUP is not available in Google Sheets. Use INDEX/MATCH or FILTER with concatenation instead. For example: =FILTER(D2:D100, (A2:A100=E2)*(B2:B100=F2)).

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.