Have you ever needed to pull a customer’s email address from one sheet just by knowing their ID? Or match product prices across two different tables without manually scrolling and copy-pasting? This is exactly what VLOOKUP was built for — and once you understand it, you’ll wonder how you managed without it.

In this guide, you’ll learn how to use VLOOKUP step by step, the most common mistakes that break it, and how the newer XLOOKUP function fixes those limitations.

---

🔍 What Does VLOOKUP Actually Do?

VLOOKUP stands for Vertical Lookup. It searches for a value in the first column of a range, then returns a value from a specified column to the right of it in that same row.

Syntax:

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

Argument	What It Means
lookup_value	The value you’re searching for (e.g., a Customer ID)
table_array	The full range containing your lookup data
col_index_num	Which column (counting from the left, starting at 1) to pull the result from
range_lookup	FALSE for exact match (almost always what you want), TRUE for approximate match

---

🛠️ Step 1: Set Up Your Lookup Table

Make sure your data is structured with the value you’ll search by (e.g., Product ID) in the leftmost column of your range. VLOOKUP can only look to the right — it cannot search backward.

🛠️ Step 2: Write the Formula

1. Click the cell where you want the result to appear.
2. Type =VLOOKUP(
3. Select the cell containing the value you want to look up (e.g., A2).
4. Select the full table range containing your data (e.g., Sheet2!A:D).
5. Enter the column number to return (e.g., 3 for the third column in that range).
6. Type FALSE for an exact match, then close the parenthesis and press Enter.

Example:

=VLOOKUP(A2, Sheet2!A:D, 3, FALSE)

This looks up the value in A2, searches for it in the first column of Sheet2!A:D, and returns the matching value from the 3rd column.

💡 Quick Tip: Always use FALSE (exact match) unless you specifically need approximate matching — TRUE requires your data to be sorted and often causes silent errors.

---

⚠️ Common VLOOKUP Errors and Fixes

Error	Cause	Fix
#N/A	Lookup value doesn’t exist in the first column	Check for typos or extra spaces; try TRIM()
#REF!	Column index number is larger than the table range	Recount your columns
Wrong result returned	range_lookup left blank (defaults to TRUE)	Always specify FALSE explicitly
Formula breaks when columns are inserted	col_index_num is a fixed number	Consider switching to XLOOKUP (see below)

---

🆚 VLOOKUP vs. XLOOKUP: What’s the Difference?

XLOOKUP is the modern replacement for VLOOKUP, available in Excel 365 and Excel 2021+. It fixes several of VLOOKUP’s biggest limitations.

XLOOKUP Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array)

Feature	VLOOKUP	XLOOKUP
Search direction	Left-to-right only	Searches in any direction
Column insert safety	Breaks if columns are inserted	Unaffected — references exact columns
Default match type	Approximate (risky)	Exact (safer default)
Missing value handling	Requires IFERROR() wrapper	Built-in if_not_found argument
Multiple return columns	Requires multiple formulas	Can return an array in one formula

Example:

=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C, "Not Found")

This searches for A2 in column A, returns the matching value from column C, and displays “Not Found” instead of #N/A if there’s no match.

💡 Quick Tip: If your version of Excel has XLOOKUP (check under the Formulas tab), it’s generally worth switching to for new spreadsheets — it’s more forgiving and easier to audit.

---

📊 Quick Summary Table

Function	Best For	Availability
VLOOKUP	Simple left-to-right lookups, maximum compatibility	All Excel versions
XLOOKUP	Flexible lookups, cleaner error handling	Excel 365 / 2021+ only

---

Conclusion

VLOOKUP remains one of the most widely used functions in Excel, and it’s essential to understand even if you eventually move to XLOOKUP. Since many workplaces still run older Excel versions, knowing both ensures you can build reliable lookups no matter which environment you’re in.

In our next guide, we’ll cover INDEX + MATCH: The Flexible Alternative to VLOOKUP for situations where you need to look up values in any direction.