VLOOKUP and XLOOKUP: How to Look Up Data Between Tables | Free Excel Course: From Beginner to Advanced

What you'll learn

Pull data from another table with VLOOKUP, step by step.
Use XLOOKUP and the INDEX + MATCH combination.
Look up tiers (commissions, rates) with an approximate match.
Fix the #N/A errors that appear even when "the data is right there."
Choose between VLOOKUP, XLOOKUP and INDEX + MATCH based on your Excel version.

📥 Practice this lesson

Look up product names and prices, including a code that doesn't exist. Expected results: P03 → Green Tea 20 ct, $9.00; P07 → Ceramic Mug, $18.00; P10 → your "not found" message.

practice-excel.xlsx · Catalog and Lookup sheets

Looking up a value in another table is, together with PivotTables, the Excel skill interviewers ask about most. With VLOOKUP and its successor XLOOKUP you can pull a product's price from its SKU, a customer's name from their account number, or the commission rate that matches a sales total.

4arguments in VLOOKUP, and the fourth should almost always be FALSE

3ways to look things up: VLOOKUP, XLOOKUP and INDEX + MATCH

#N/Ameans "not found," and it usually has one of four causes

The typical case
VLOOKUP step by step
XLOOKUP
INDEX + MATCH
Tiers and #N/A errors

The typical case

You have a Products sheet with the catalog of an office-furniture store:

	A	B	C
1	SKU	Description	Price
2	P-001	Ergonomic chair	189
3	P-002	Standing desk	245
4	P-003	LED desk lamp	39

On another sheet, Orders, you only enter the SKU and the quantity. You want the description and price to fill in automatically.

VLOOKUP step by step

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

Lookup value: what you know (the SKU on the order, say A2).
Table array: the table to search. The lookup value must be in the first column of this range.
Column index number: which column of the table to return, counting from 1.
FALSE: exact match. Almost always what you want.

Description: =VLOOKUP(A2, Products!$A$2:$C$500, 2, FALSE)
Price:       =VLOOKUP(A2, Products!$A$2:$C$500, 3, FALSE)

Notice the $ signs: the table must stay locked so it doesn't shift when you copy the formula down (lesson 3). If the catalog is an Excel table named Products, it's even simpler: =VLOOKUP(A2, Products, 3, FALSE).

The most expensive omission in Excel

If you leave out the fourth argument, VLOOKUP assumes TRUE (approximate match) and can return the price of a different product without any error. Always type FALSE (or 0) when you look up codes, names or ID numbers.

VLOOKUP's three limitations

It only looks to the right: it can't return a column to the left of the lookup column.
The column number is hard-coded: if someone inserts a column in the catalog, the formula returns the wrong data.
When it doesn't find the value it returns #N/A, so you have to wrap it in IFERROR.

1. What does the last argument, FALSE, mean in =VLOOKUP(A2,Products!$A$2:$D$200,3,FALSE)?

Return a blank if the value isn't found
Search from the bottom up
Look for an exact match

FALSE asks for an exact match, which is what you need for codes, names or ID numbers. TRUE is for tiered tables sorted in ascending order.

XLOOKUP: the modern version

Available in Microsoft 365, Excel 2021 and later, Excel for the web and Google Sheets. It solves all three limitations:

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

=XLOOKUP(A2, Products!A:A, Products!C:C, "SKU not found")

You point to the return column directly, with no counting.
It can return columns to the left.
Exact match is the default.
It has a built-in "not found" message, no IFERROR needed.

It can also return several columns at once: =XLOOKUP(A2, Products!A:A, Products!B:C) fills in the description and the price in two adjacent cells.

And it can search from the bottom up. Its sixth argument, search_mode, set to -1 starts at the last row. If your order log is sorted by date, =XLOOKUP(A2, Orders!B:B, Orders!E:E, "No orders", 0, -1) returns the most recent order amount for the customer in A2, something VLOOKUP can't do without helper columns.

Looking up with two criteria

If the price depends on the SKU and the size, join both with &: =XLOOKUP(A2&B2, Products!$A$2:$A$500&Products!$B$2:$B$500, Products!$D$2:$D$500). XLOOKUP compares "P-001M" with every SKU+size in the list and returns the price from the matching row.

Which one should I use?

If you and everyone you share the file with have Microsoft 365 or Excel 2021+, use XLOOKUP. If someone will open it in Excel 2016 or 2019, use VLOOKUP (or INDEX + MATCH), because XLOOKUP shows #NAME? in those versions.

2. Which VLOOKUP limitation does XLOOKUP solve?

VLOOKUP can't return a column to the left of the lookup column
VLOOKUP doesn't work with text
VLOOKUP can't use ranges on another sheet

VLOOKUP always searches the first column and returns columns to its right. XLOOKUP uses separate lookup and return ranges, in any direction.

INDEX + MATCH: the classic combination

Before XLOOKUP, power users combined two functions to look up in any direction. It works in every version:

=INDEX(Products!C:C, MATCH(A2, Products!A:A, 0))

MATCH finds the position (row) of the SKU, and INDEX returns whatever sits at that position in the price column. The 0 at the end of MATCH means exact match.

Which of the three should I choose?

VLOOKUPThe old standby

Works in: Every version.
Ease: ●●●●
Robustness: ●●●●
Best if: People open the file in older Excel and the table structure never changes.

Compatible

XLOOKUPThe modern choice

Works in: Microsoft 365, Excel 2021 and later, Google Sheets.
Ease: ●●●●
Robustness: ●●●●
Best if: Everyone has a recent version. It's the first choice today.

Recommended

INDEX + MATCHThe expert's tool

Works in: Every version.
Ease: ●●●●
Robustness: ●●●●
Best if: You need to look left in an Excel without XLOOKUP.

Flexible

Approximate match: tiers and brackets

Approximate match has one legitimate and very powerful use: assigning tiers. Picture a commission schedule:

	H	I
1	Sales from	Commission
2	$0	2%
3	$5,000	4%
4	$15,000	6%
5	$30,000	8%

=VLOOKUP(B2, $H$2:$I$5, 2, TRUE)

For sales of $18,400, Excel finds the largest "from" value that doesn't exceed $18,400 (that's $15,000) and returns 6%. Requirement: the first column must be sorted smallest to largest. With XLOOKUP: =XLOOKUP(B2, $H$2:$H$5, $I$2:$I$5, , -1), where -1 means "exact match or next smaller item."

This replaces a four-level nested IF, and if the tiers change next year, you only edit the table.

Worked example: commissions at a beverage distributor in Phoenix

A beverage distributor in Phoenix uses that schedule for its five reps. Monthly sales are in B2:B6; the rate comes from C2: =VLOOKUP(B2, $H$2:$I$5, 2, TRUE) and the commission from D2: =B2*C2:

Rep	Sales	Tier applied	Rate	Commission
Maria	$4,200	From $0	2%	$84
Jake	$9,800	From $5,000	4%	$392
Tyler	$15,000	From $15,000	6%	$900
Sophia	$27,500	From $15,000	6%	$1,650
Omar	$31,200	From $30,000	8%	$2,496
Total commissions				$5,522

Commission for the month by rep

Maria$84

Jake$392

Tyler$900

Sophia$1,650

Omar$2,496

Look at Tyler: he sold exactly $15,000 and earns 6%, because "from $15,000" includes that value. And notice the jump between Sophia and Omar: once a rep passes $30,000, the higher rate applies to the whole amount, not just the part above $30,000. If the company wanted graduated tiers that work like federal income tax brackets, it would need a different formula.

3. With that schedule, a rep sells exactly $5,000. What rate does =VLOOKUP(5000, $H$2:$I$5, 2, TRUE) return?

2%, because $5,000 doesn't exceed the first tier yet
4%, because it finds the largest "from" value that doesn't exceed $5,000, which is $5,000 itself
#N/A, because approximate match doesn't accept exact values

Approximate match returns the last value less than or equal to the one you're looking for. Since $5,000 is in the table, it takes that row: 4%, or $200 in commission.

Why #N/A appears even though "the data is right there"

Invisible spaces

"P-001 " isn't equal to "P-001".

Fix: clean them with =TRIM(A2).

Number vs. text

Account 1050 stored as a number doesn't match "1050" stored as text.

Fix: check both columns' alignment and convert with VALUE.

Shifted table

You forgot the $ signs and, as you copied down, the range left out the first rows.

Fix: lock it with F4 or use an Excel table.

Wrong first column

In VLOOKUP, the lookup value isn't in the first column of the table array.

Fix: start the range at the code column or use XLOOKUP.

4. SKU "A-105" appears in both tables, but VLOOKUP returns #N/A. What's the most likely cause?

Excel can't look up codes with hyphens
The table has too many rows
There are invisible spaces, or a number stored as text, in one of the tables

Most "unexplainable" #N/A errors come from extra spaces or number-vs-text mismatches. TRIM and VALUE help fix them.

Practice exercise

Create a Products sheet with 6 products (SKU, Description, Price) and an Orders sheet with the columns SKU, Quantity, Description, Price and Amount.

Fill in Description and Price with VLOOKUP or XLOOKUP.
Calculate the Amount.
Type a SKU that doesn't exist and make "Check SKU" appear instead of #N/A.

See solution

Description: =IFERROR(VLOOKUP($A2, Products!$A$2:$C$7, 2, FALSE), "Check SKU")
Price:       =IFERROR(VLOOKUP($A2, Products!$A$2:$C$7, 3, FALSE), 0)
Amount:      =B2*D2

With XLOOKUP:
Description: =XLOOKUP($A2, Products!$A$2:$A$7, Products!$B$2:$B$7, "Check SKU")

Summary

VLOOKUP: lookup value, locked table, column number and FALSE.
XLOOKUP is more flexible and safer, but needs Excel 2021 or Microsoft 365.
Approximate match is for tiers, with the table sorted smallest to largest.
Most #N/A errors come from extra spaces or numbers stored as text.
In a tiered schedule, a value exactly on the boundary belongs to the tier that starts there.

Next lesson: SUMIFS, COUNTIFS and Excel Text and Date Functions. You'll sum and count with several criteria, filter by date ranges, clean up text and calculate with dates.