XLOOKUP in Excel: find matching values left or right

What XLOOKUP does

XLOOKUP searches for a lookup value inside a lookup array and returns the matching item from a return array. In plain language: find this ID, then give me the price, name, status, owner, or date on the same row.

Its basic syntax is `=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`. The first three arguments are required. The optional arguments control what happens when no match is found, whether the match must be exact, and how Excel searches.

Use exact match for most everyday work. For example, `=XLOOKUP(A2,F:F,G:G,"Not found",0)` looks for the value in A2 inside column F and returns the related value from column G. The `0` means exact match. The custom `"Not found"` message is easier to understand than a raw `#N/A`.

Arguments explained

`lookup_value` is the thing you are searching for, such as a product code in A2. `lookup_array` is the range that contains possible matches, such as F:F. `return_array` is the range that contains the answer you want, such as G:G.

`if_not_found` is optional but helpful. It lets you return text like `"Missing"` instead of an error. `match_mode` controls exact or approximate behavior. Use `0` for exact match. Approximate options are useful for tiers and thresholds, but they should be chosen deliberately. `search_mode` controls search direction, including searching from last to first.

Unlike VLOOKUP, XLOOKUP can return values from columns to the left or right of the lookup column. If employee names are in column B and employee IDs are in column D, XLOOKUP can search D:D and return B:B without rearranging the table.

Practical examples

A beginner example is looking up an item price. If A2 contains SKU `BB20`, F:F contains SKU codes, and G:G contains prices, use `=XLOOKUP(A2,F:F,G:G,"Missing",0)`. Excel searches for A2 in F:F and returns the price from G:G on the matching row.

A business example is assigning account owners. Suppose column B contains customer IDs in a sales export, column J contains customer IDs in a reference table, and column K contains account owners. `=XLOOKUP(B2,J:J,K:K,"Unassigned",0)` returns the owner for that customer or clearly marks missing IDs.

When to use it

Use XLOOKUP when you need a clear exact match lookup, a custom missing-value message, or a lookup that returns data from either side of the lookup column. It is also useful when you want formulas that are easier to read than nested INDEX and MATCH formulas.

Do not use XLOOKUP for simple arithmetic, summaries by category, or cases where a PivotTable or SUMIFS answers the question more directly. Also remember that XLOOKUP is available in modern Excel; older workbooks may still rely on VLOOKUP, INDEX, and MATCH for compatibility.

Related functions

VLOOKUP is still common and worth understanding, especially in older files. INDEX and MATCH are useful when you need flexible lookup logic or compatibility. IFERROR can also handle missing lookup results, although XLOOKUP's `if_not_found` argument is often cleaner.

To practice lookup thinking, compare XLOOKUP with VLOOKUP, then try lookup exercises inside Calcu where every formula updates against realistic tables.