VLOOKUP & XLOOKUP

A lookup finds a value in one table and brings back related information. Type a product code and get its price; type a roll number and get the student's name; type a sales amount and get the commission rate from a slab table. Lookups connect separate lists, and they are among the most-used formulas in any office.

In this lesson you will learn XLOOKUP, the modern lookup function, first. Then you will learn VLOOKUP, which you will still find in millions of existing workbooks, and see exactly why XLOOKUP replaced it.

The sample sheet

A general store in Rohtak keeps its product list in A1:E6 and types a product code into H2 to fetch details:

XLOOKUP: the modern way

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

The first three arguments are all you need most of the time: what to find, where to find it, and what to bring back. With P104 in H2:

=XLOOKUP(H2, A2:A6, D2:D6)

499

Excel looked for P104 in A2:A6, found it in the 4th position, and returned the 4th value of D2:D6. XLOOKUP is available in Microsoft 365, Excel 2021 and later, Excel for the web, and Google Sheets.

When the value is not found

By default, a missing code returns #N/A. The fourth argument replaces that with your own message:

=XLOOKUP(H2, A2:A6, D2:D6, "Code not found")

For a code such as P109, the cell shows "Code not found" instead of an error.

Looking to the left

The return column can be anywhere, including to the left of the lookup column. To find the code of a product by its name:

=XLOOKUP("Steel tiffin box", B2:B6, A2:A6)

P104

Returning several columns at once

If the return array is several columns wide, the answer spills into neighbouring cells. One formula in I2 fills three cells:

=XLOOKUP(H2, A2:A6, B2:D6)

Slab rates with approximate match

Many Indian business rules are slabs: commission, discounts, delivery charges, tax brackets. A sales team earns commission by monthly sales. This made-up slab table sits in M1:N5:

Each row says "from this amount upwards". There is no exact match for 84000, so ask for the exact match or the next smaller item with match_mode -1:

=XLOOKUP(84000, M2:M5, N2:N5, , -1)

0.02

84000 falls between 50000 and 100000, so the smaller boundary, 50000, wins and the rate is 2%. For 126000 the result is 3.5%, and for 250000 it is 5%. The empty argument between the commas skips if_not_found. Match mode 1 does the opposite (exact or next larger), and 2 allows the * and ? wildcards.

The sixth argument, search_mode, sets the direction: -1 searches from the bottom up. In a price-history list where new rows are added at the bottom, =XLOOKUP(H2, A2:A50, D2:D50, , 0, -1) returns the latest price for a code.

VLOOKUP: the older function

VLOOKUP ("vertical lookup") was the standard lookup for decades, so you will meet it in almost every office workbook.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

It searches the first column of the table and returns the value from the column number you give. The price of P104 is in the 4th column of A2:E6:

=VLOOKUP(H2, A2:E6, 4, FALSE)

499

That FALSE at the end is essential. It asks for an exact match.

Why XLOOKUP replaced VLOOKUP

VLOOKUP is still fine for the slab-table job when its first column is sorted: =VLOOKUP(84000, M2:N5, 2, TRUE) returns 2%. Its sideways twin HLOOKUP searches a top row instead of a first column. Use XLOOKUP for new work whenever everyone who opens the file has Excel 2021 or later; use VLOOKUP or INDEX & MATCH if the file must work in Excel 2019 or older.

Looking up from another sheet

The product list usually lives on its own sheet. Point the ranges at that sheet, and lock them with $ so they do not move when you copy the formula down an order list:

=XLOOKUP(A2, Products!$A$2:$A$6, Products!$D$2:$D$6, 0)

Here a missing code returns 0 so that the bill total still works. In Excel Tables you will replace these ranges with names that grow by themselves.

Common mistakes

• Leaving out FALSE in VLOOKUP. You get wrong answers instead of errors. Always add it for exact matches.
• Extra spaces. "P104 " with a trailing space does not match "P104". Clean the data with TRIM.
• Numbers versus text. A code typed as the number 101 does not match the text "101". Make both columns the same type.
• Ranges of different sizes in XLOOKUP. A2:A6 and D2:D7 give #VALUE!.
• Unsorted slab tables. Approximate-match lookups expect the boundaries in ascending order with VLOOKUP; keep slab tables sorted anyway, for readability.

Key points

• XLOOKUP(what, where, return) finds a value and returns the matching item; it defaults to exact match.
• Its fourth argument replaces #N/A with your message; match mode -1 handles slab tables.
• XLOOKUP can look left, return several columns, and search from the bottom up.
• VLOOKUP(value, table, column_number, FALSE) is the older way; never omit FALSE for exact lookups.
• Use XLOOKUP in Excel 2021, 2024, Microsoft 365 and Google Sheets; use VLOOKUP or INDEX/MATCH for older versions.

=XLOOKUP(H5, $A$2:$A$6, $D$2:$D$6, 0)
=I5*J5
=SUM(K5:K7)

J5: 165   K5: 495
J6: 2150  K6: 2150
J7: 0     K7: 0
K8: 2645