Excel VLOOKUP Tutorial: Find Data Across Spreadsheets

Microsoft Excel’s VLOOKUP function is one of the most widely used formulas in business spreadsheets. Whether you’re matching employee records, pulling product prices from a catalog, or cross-referencing data between sheets, VLOOKUP lets you search a table and return any value from a matching row — without manual lookup.

If you’re new to Excel formulas, start with our Excel tutorial for beginners before diving into VLOOKUP. Once you understand cell references and basic functions, VLOOKUP becomes straightforward.

This tutorial covers the complete syntax, step-by-step examples, common errors, and when to upgrade to XLOOKUP — the modern replacement available in Microsoft Excel 365 and Excel 2021.

Key Takeaways

• VLOOKUP searches the leftmost column of a range and returns a value from a specified column to the right
• Always use FALSE for exact match — TRUE (approximate match) returns wrong results unless your data is sorted
• XLOOKUP is more flexible and available in Excel 365 / Excel 2021+; VLOOKUP still works in all Excel versions

---

What Is VLOOKUP and How Does It Work?

VLOOKUP (Vertical Lookup) is a Microsoft Excel function that searches for a value in the first column of a range, then returns a value from any column in the same row. The “V” stands for vertical — it scans down a column, not across a row.

The full syntax, as documented on Microsoft’s VLOOKUP support page, is:

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

Parameters:

Parameter	Required	Description
lookup_value	Yes	The value you want to find (a cell reference, number, or text)
table_array	Yes	The range containing your data — lookup column must be the leftmost
col_index_num	Yes	The column number (1-based) in table_array to return
range_lookup	No	FALSE = exact match (recommended); TRUE = approximate match

How VLOOKUP works internally: Excel scans down the first column of table_array looking for lookup_value. When it finds a match, it moves right to the column specified by col_index_num and returns that value.

Critical limitation: VLOOKUP can only look to the right. The lookup column must always be the first (leftmost) column in your range. If you need to look left, use XLOOKUP (covered below).

---

How Do I Use VLOOKUP Step by Step?

To use VLOOKUP, identify your lookup value, define the data range with the lookup column first, specify which column to return, and set FALSE for exact match.

Here is a practical walkthrough using a product price table.

Scenario: You have a product catalog in columns A–C (Product Code, Product Name, Price). You want to look up a price by entering a product code in cell E2.

Your data (Sheet range A1:C6):

A	B	C
Product Code	Product Name	Price
P001	Wireless Mouse	29.99
P002	USB Keyboard	49.99
P003	HDMI Cable	12.99
P004	Webcam	79.99

Step 1: Click the cell where you want the result — for example, F2.

Step 2: Type the VLOOKUP formula:

=VLOOKUP(E2, A1:C6, 3, FALSE)

Step 3: Understand what each part does:

• E2 — the product code you’re searching for
• A1:C6 — the full data range (Product Code is column A, the leftmost)
• 3 — return column 3 (Price)
• FALSE — exact match required

Step 4: Press Enter. If E2 contains “P003”, the formula returns 12.99.

Step 5: Lock the table range for copying. If you plan to copy the formula down, use absolute references to prevent the range from shifting:

=VLOOKUP(E2, $A$1:$C$6, 3, FALSE)

The dollar signs keep A1:C6 fixed no matter where you paste the formula.

---

Common VLOOKUP Examples

a. Look Up Employee Name by ID

You have an employee table with IDs in column A and names in column B. To find the name for the ID in cell D2:

=VLOOKUP(D2, A2:B100, 2, FALSE)

This returns the value from the second column (Name) where the ID matches D2.

b. Find Price by Product Code

Using a named range makes formulas easier to read. If your product table is named ProductCatalog:

=VLOOKUP("P002", ProductCatalog, 3, FALSE)

Hard-coded text values in VLOOKUP must be wrapped in double quotes. This returns the price for product P002.

c. VLOOKUP with Approximate Match (Grade Ranges)

When range_lookup is set to TRUE, VLOOKUP finds the largest value less than or equal to your lookup value. The lookup column must be sorted ascending. This is useful for grade scales or tax brackets.

Grade table (A1:B5):

Score	Grade
0	F
60	D
70	C
80	B
90	A

=VLOOKUP(85, A1:B5, 2, TRUE)

Returns B — because 85 is greater than or equal to 80 but less than 90.

d. VLOOKUP Across Sheets

To look up data from a different worksheet, include the sheet name followed by an exclamation mark in the table_array reference:

=VLOOKUP(A2, Sheet2!A:C, 2, FALSE)

This searches column A of Sheet2 for the value in A2 on the current sheet, then returns column B. If the sheet name contains spaces, wrap it in single quotes: 'Price List'!A:C.

---

What Is the Difference Between VLOOKUP and XLOOKUP?

XLOOKUP is a newer Microsoft Excel function (available in Excel 365 and Excel 2021+) that eliminates several core limitations of VLOOKUP. For the full XLOOKUP syntax, see Microsoft’s XLOOKUP documentation.

Feature	VLOOKUP	XLOOKUP
Lookup direction	Left-to-right only	Any direction (left, right, up, down)
Default match type	Approximate (TRUE) — error-prone	Exact match by default
Column reference	Column index number (breaks when columns inserted)	Direct return range (robust)
Not-found handling	Returns #N/A error	Built-in [if_not_found] parameter
Multiple results	Single column only	Can return entire rows or arrays
Availability	All Excel versions	Excel 365, Excel 2021+ only
Performance	Slower on large datasets	Faster binary search options

The key practical difference: if someone inserts a column into your data range, a VLOOKUP col_index_num silently returns the wrong column. XLOOKUP references the return range directly, so it stays correct.

---

When Should I Use XLOOKUP Instead of VLOOKUP?

Use XLOOKUP when you need to look left, handle missing values gracefully, or return data from multiple columns at once.

XLOOKUP syntax (full reference on Microsoft Learn):

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

Parameters:

• lookup_value — value to find
• lookup_array — the column/row to search (not the full table)
• return_array — the column/row to return values from
• [if_not_found] — text or value to return when no match found (replaces #N/A)
• [match_mode] — 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger
• [search_mode] — 1 = first to last (default), -1 = last to first

Example 1: Look up a price (equivalent to the VLOOKUP example above):

=XLOOKUP(E2, A2:A6, C2:C6)

No column index number needed — just point directly to the return column.

Example 2: Look left — find a product code by name:

=XLOOKUP("Webcam", B2:B6, A2:A6)

VLOOKUP cannot do this. XLOOKUP searches column B and returns the value from column A (to the left).

Example 3: Return a custom message instead of an error:

=XLOOKUP(E2, A2:A6, C2:C6, "Product not found")

When no match exists, the formula returns “Product not found” instead of #N/A.

When to stick with VLOOKUP: If your workbook must be compatible with Excel 2019 or earlier, or if you’re sharing files with users who don’t have Microsoft 365, VLOOKUP is the safe choice. It works in every Excel version since Excel 2003.

If you’re running Microsoft Excel 365 or planning to upgrade, check our Office 2024 lifetime license guide — a one-time purchase that includes both Excel 2024 and XLOOKUP support. Office 2024 Pro Plus is available for $199.99, or Office 2021 Pro Plus for $64.99 if XLOOKUP compatibility is sufficient.

---

Why Isn’t My VLOOKUP Working?

Most VLOOKUP errors fall into six categories. Use this table to diagnose and fix the issue:

Error / Symptom	Cause	Fix
#N/A	Lookup value not found in the first column	Verify the value exists; check for extra spaces using TRIM(); confirm exact match with FALSE
#N/A with numbers	Number stored as text (or vice versa)	Use VALUE() to convert: =VLOOKUP(VALUE(E2), A:C, 2, FALSE)
#REF!	col_index_num is larger than the number of columns in table_array	Count your columns — if table_array is A:C (3 columns), col_index_num cannot exceed 3
#VALUE!	col_index_num is less than 1, or is text instead of a number	Ensure col_index_num is a positive integer ≥ 1
Wrong result returned	range_lookup is TRUE (approximate match) but data is not sorted	Always use FALSE for exact match unless you specifically need approximate matching on sorted data
Formula returns value from wrong column	A column was inserted or deleted in the data range after the formula was written	Update col_index_num to match the new column position, or switch to XLOOKUP

Tip for #N/A errors: Wrap VLOOKUP in IFERROR to display a friendlier message:

=IFERROR(VLOOKUP(E2, $A$1:$C$6, 3, FALSE), "Not found")

This prevents error values from cascading through your spreadsheet and makes dashboards cleaner.

Tip for text/number mismatches: If your lookup values come from an external import or CSV, they often arrive as text-formatted numbers. Use TRIM() and VALUE() together to clean them before lookup:

=VLOOKUP(VALUE(TRIM(E2)), $A$1:$C$6, 3, FALSE)

For more complex data analysis once your lookups are working, see our Excel pivot table tutorial to summarize and group results dynamically.

---

Frequently Asked Questions

---