The XLOOKUP Syntax

XLOOKUP is the modern replacement for VLOOKUP and HLOOKUP. It finds a value in one column (or row) and returns the matching value from another.

Its biggest win: you tell it where to search and what to return as two separate ranges. No counting columns, no worrying about which side your answer sits on.

XLOOKUP is available in Excel 365, Excel 2021, and Google Sheets.

The simplest XLOOKUP needs just three pieces of information:

• lookup_value - what you are searching for
• lookup_array - the range to search in
• return_array - the range to pull the answer from

Read the formula as: find this value, in this column, and give me the matching item from that column.

Imagine a product list. Column A holds product names and column B holds prices. You want the price of "Keyboard".

You search for the name in A2:A20 and return the price from B2:B20. XLOOKUP scans the lookup array, finds "Keyboard", then returns whatever sits in the same row of the return array.

Hard-coding "Keyboard" inside the formula is fragile. It is far better to point the lookup value at a cell.

If the user types the product name into D2, your formula reacts automatically when they change it. This is how live lookup tools and dashboards work.

The lookup array and return array should have the same height (same number of rows). XLOOKUP matches them row by row.

If A2:A20 is 19 rows, the return array must also be 19 rows. A mismatch produces a #VALUE! error because XLOOKUP cannot pair the rows correctly.

The return array does not have to be next to the lookup array. Suppose names are in column A but you want the stock count in column E.

Just point the return array at E2:E20. XLOOKUP does not care about the gap between columns, unlike VLOOKUP which counts columns rightward.

One of the friendliest things about XLOOKUP is that it looks for an exact match by default.

VLOOKUP defaulted to approximate match, which silently returned wrong answers when people forgot the final FALSE. XLOOKUP flips this: you get the exact match unless you deliberately ask for something else.

XLOOKUP also handles horizontal data. If month names sit across row 1 and sales sit across row 2, you can search the row instead of a column.

The same formula structure works: lookup value, the row of months, the row of sales. One function replaces both VLOOKUP and HLOOKUP.

Because XLOOKUP returns a single value, you can drop it straight into a calculation.

For example, multiply a looked-up unit price by a quantity in E2. The lookup runs first, returns the price, and the multiplication uses that result. This keeps your sheet compact and readable.

You can use full-column references like A:A and B:B so the formula keeps working as new rows are added.

This is convenient, but on very large sheets it can be slightly slower. For tidy data, referencing the exact range or an Excel Table is a good habit.

Compared with VLOOKUP, XLOOKUP is:

• Direction-free - return columns can be left or right of the lookup
• Safer - exact match by default
• Stable - inserting columns does not break it
• Simpler - no column-index counting

Once you learn the three core arguments, most everyday lookups become effortless.

Test your understanding of the basic XLOOKUP syntax.

You learned the three core arguments of XLOOKUP:

• lookup_value - what to find
• lookup_array - where to search
• return_array - what to return

It defaults to an exact match, the return range can sit anywhere, and it works across both columns and rows. Next you will make it handle missing values gracefully.