XLOOKUP Match Any Column: How It Works

Yes, XLOOKUP can match any column in a lookup table, not just the leftmost one. You simply set the third argument (return_array) to the column you want results from, and XLOOKUP searches the column you specify in the second argument (lookup_array) — it doesn’t care if that column is on the left, right, or in the middle. This is a major improvement over VLOOKUP, which always searches the first column of a table.

Background: Why XLOOKUP Changed the Game

For decades, Excel users relied on VLOOKUP and HLOOKUP to find values in a table. VLOOKUP had a hard limitation: the lookup value had to be in the first column of the selected range, and the result had to be to the right of it. If your data had ID numbers in column C and names in column A, you couldn’t use VLOOKUP to find a name by ID without rearranging your columns. INDEX/MATCH was the workaround, but it required two functions and a deeper understanding of array formulas. XLOOKUP, introduced in 2020 for Microsoft 365 and Excel 2021, simplified everything by decoupling the lookup column from the return column. You can now point to any column as the lookup array and any column as the return array, regardless of position.

How XLOOKUP Works with Any Column

The syntax is straightforward: XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The key is that lookup_array and return_array are independent ranges. For example, if you have a table where column A is Product ID, column B is Product Name, and column C is Price, you can look up a price by entering the product name:

Lookup value: the product name in a cell (e.g., “Widget”)
Lookup array: B2:B100 (the Product Name column)
Return array: C2:C100 (the Price column)

This returns the price from column C even though the lookup was on column B. You can also search from right to left, which VLOOKUP could never do. For instance, to find a product ID from a price, set lookup_array to C2:C100 and return_array to A2:A100.

How to Choose the Right Columns

When setting up XLOOKUP, think about what you’re searching for and what you want back. The lookup_array should be the column that contains the unique identifier you have in hand (like an email, SKU, or code). The return_array is the column with the information you need. There’s no requirement that the return column be to the right; it can be anywhere in the worksheet, even on a different sheet. Just ensure both arrays have the same number of rows, or XLOOKUP returns a #VALUE! error. Also, be careful with merged cells — they can cause mismatched array sizes and unexpected results.

Common Problems and How to Avoid Them

One frequent issue is getting #N/A when the lookup value isn’t found. Use the fourth argument to provide a friendly message like “Not found” instead of an error. Another problem is duplicate values in the lookup column — XLOOKUP returns the first match by default, so if duplicates exist, you may get the wrong record. To handle this, either ensure uniqueness or use the search_mode argument to find the last match (search_mode = -1). Also, watch out for data type mismatches: if your lookup value is a number but the lookup column contains text-formatted numbers, XLOOKUP won’t match them. Use the VALUE or TEXT functions to normalize formats.

XLOOKUP vs. VLOOKUP vs. INDEX/MATCH

Compared to VLOOKUP, XLOOKUP is more flexible and easier to read. VLOOKUP requires a column index number, which breaks if you insert or delete columns. XLOOKUP uses direct range references, so it automatically adjusts. INDEX/MATCH is still powerful, but XLOOKUP often replaces it because it’s a single function with built-in error handling. However, XLOOKUP is only available in Excel 2021 and Microsoft 365; if you’re using Excel 2019 or earlier, you’ll need to stick with INDEX/MATCH or VLOOKUP. For compatibility with older versions, you might avoid XLOOKUP in shared workbooks.

Real-World Examples and Tips

Imagine you have a sales report with columns: Date, Salesperson, Region, and Amount. To find the amount for a specific salesperson, set lookup_array to the Salesperson column and return_array to the Amount column. You can also use XLOOKUP to return multiple columns by using a range for return_array, like C2:D100, which returns both Region and Amount as a spilled array. This is handy for building dashboards. Another tip: use XLOOKUP with wildcards (match_mode = 2) to find partial matches, like searching for “Wi*” to match “Widget”. Just be aware that wildcard searches are case-insensitive by default.

Frequently Asked Questions

Can XLOOKUP return a value from a column to the left of the lookup column?

Yes, absolutely. The return_array can be any column, left or right. This is one of XLOOKUP’s biggest advantages over VLOOKUP.

What if my lookup column has duplicates?

By default, XLOOKUP returns the first match. If you need the last match, set the search_mode argument to -1. For all matches, you’d need to use FILTER or another function.

Does XLOOKUP work with wildcards?

Yes, set match_mode to 2 to enable wildcard matching. Use * for multiple characters and ? for a single character. This works only with text data.

Why do I get #VALUE! instead of a result?

This usually means the lookup_array and return_array have different sizes. Check that both ranges have the same number of rows. Also, ensure you haven’t selected entire columns unless both are the same size.

Is XLOOKUP available in Excel 2016 or 2019?

No, XLOOKUP is only available in Excel 2021 and Microsoft 365. For older versions, use INDEX/MATCH or VLOOKUP.

Can I use XLOOKUP to return a whole row?

Yes, if you set return_array to a range that spans multiple columns in the same row, XLOOKUP will return all those values as a horizontal array. For example, return_array = B2:D2 returns three cells.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.

More cards in this drawer

The whole Numbers and Powers drawer →