XLOOKUP in Excel: A Practical Guide With a Worked Example

By Camderson Academy

If you have ever pulled a price, a name or a status from one table into another, you have needed a lookup. XLOOKUP is Excel's modern way to do it. It is easier to read than VLOOKUP, does not break when columns move and lets you say what to show when nothing is found. This guide explains the function in plain English and walks through a worked example you can rebuild in five minutes.

The syntax, in plain English

Microsoft's documentation gives the syntax as:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The first three arguments are required. The ones in square brackets are optional.

• lookup_value: what you are looking for, such as a product code.
• lookup_array: the column to search in, such as the list of product codes.
• return_array: the column to bring back, such as the list of prices.
• if_not_found (optional): what to show when there is no match, such as "Not found". If you leave it out, Excel shows #N/A.
• match_mode (optional): how to match. The default is 0, an exact match.
• search_mode (optional): which direction to search, for example first to last or last to first.

Read it as a sentence: "Find this, in this column, and give me the value from that column."

Find this, in this column, and give me the value from that column. That one sentence is most of XLOOKUP.

Why it beats VLOOKUP for most work

• No column numbers. VLOOKUP asks you to count columns, so inserting a new column can quietly break it. XLOOKUP points at the return column directly.
• Looks left or right. The return column can sit on either side of the lookup column.
• Exact match by default. VLOOKUP's default approximate match is a common source of wrong answers. XLOOKUP defaults to exact.
• Built-in "not found" message. No need to wrap the formula in IFERROR for the most common case.

One caution about compatibility: according to Microsoft, XLOOKUP is available in Excel for Microsoft 365, Excel 2021 and Excel 2024, but not in Excel 2016 or Excel 2019. If you share files with people on older versions, check before relying on it.

A worked example

Imagine you manage orders for a small office supply business. You have a product list on one sheet and an order log on another, and you want each order line to show the correct unit price.

Product list (sheet "Products"):

Order log (sheet "Orders"): product codes are in column B, starting in row 2. In cell C2, enter:

=XLOOKUP(B2, Products!A2:A4, Products!C2:C4, "Not found")

If B2 contains P-205, the formula returns 14.50. If someone types a code that does not exist, such as P-999, the cell shows "Not found" instead of an error, which makes data entry problems easy to spot.

To make the formula easy to copy down the column, lock the ranges with dollar signs, or better, turn the product list into an Excel table and refer to its columns by name. With a table named Products, the formula reads almost like a sentence:

=XLOOKUP(B2, Products[Product code], Products[Unit price], "Not found")

The same lookup in VLOOKUP and XLOOKUP

Seeing the two side by side makes the difference clear. To find the price for the code in B2 with VLOOKUP, you would write:

=VLOOKUP(B2, Products!A2:C4, 3, FALSE)

That formula depends on the number 3, meaning "the third column of the range," and on remembering FALSE to force an exact match. If someone later inserts a column for supplier names between product name and price, the 3 now points at the wrong column, and the formula quietly returns supplier names instead of prices. The XLOOKUP version points straight at the price column, so the same change does not break it.

Three practical tips

• Return several columns at once. Microsoft notes that XLOOKUP can return an array with multiple items, so pointing the return_array at two adjacent columns, such as name and price, brings back both with one formula.
• Clean your keys. Extra spaces and text-versus-number mismatches are common reasons a lookup fails. TRIM can remove stray spaces.
• Use the not-found message on purpose. "Check code" is more helpful to a colleague than a blank or an error.

Template: XLOOKUP planning sheet

The takeaway

XLOOKUP replaces most VLOOKUP work with a clearer formula: find this, in this column, return that column, and say what to show if nothing is found. Learn the first four arguments and you will cover the majority of everyday lookups.

Sources

• Microsoft Support, "XLOOKUP function"

The business and figures in the example are fictional.