Tutorials›Excel›VLOOKUP & XLOOKUP
🟢 Free Demo
Excel TutorialTopic 4 of 31

VLOOKUP & XLOOKUP

Look up values across tables with VLOOKUP and the modern XLOOKUP — essential for joining data in Excel.

✅ What You Will Learn

›How VLOOKUP finds data by matching a key column
›The meaning of the col_index_num and range_lookup arguments
›Why XLOOKUP is more powerful and flexible than VLOOKUP
›How to handle #N/A errors with IFERROR
›Common real-world lookup scenarios analysts face daily

VLOOKUP (Vertical Lookup) searches the leftmost column of a table for a value and returns data from a specified column to the right. It is one of the most-used functions in Excel and appears in virtually every analyst technical interview.

The function has four arguments: what to look for, where to look, which column to return, and whether to match exactly or approximately. The fourth argument should almost always be FALSE (exact match) — the default TRUE (approximate match) causes subtle bugs that are hard to detect.

XLOOKUP, introduced in Microsoft 365, fixes several VLOOKUP limitations. It can look left, search from the bottom up, return a range of columns, and has a built-in default for when no match is found. If you have Microsoft 365, use XLOOKUP. If you are on an older version, VLOOKUP remains essential.

📋 Products table (lookup source)

Product IDProduct NameCategoryPrice (₹)Stock
P001Laptop ProElectronics5200015
P002Wireless MouseAccessories1200200
P003USB-C HubAccessories280085
P004Monitor 24"Electronics1850030
P005Mechanical KeyboardAccessories450060

Syntax

EXCEL SYNTAX
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  range_lookup: FALSE = exact match (use this always for data lookups)
                TRUE  = approximate match (for sorted numeric ranges only)

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

Examples

Example 1VLOOKUP — find a product name from its ID
=VLOOKUP("P003", A2:E6, 2, FALSE)
OUTPUT
USB-C Hub
💡

The 2 means "return the value from the 2nd column of the table (A2:E6)". Always use FALSE as the last argument for data lookups.

Example 2VLOOKUP with IFERROR — handle missing products gracefully
=IFERROR(VLOOKUP(H2, A2:E6, 4, FALSE), "Product not found")
OUTPUT
If H2 = "P002" → 1200   |   If H2 = "P999" → "Product not found"
💡

Without IFERROR, unmatched lookups display #N/A. Always wrap lookup functions in IFERROR in reports shared with stakeholders.

Example 3XLOOKUP — same lookup, but returns from any column and handles missing natively
=XLOOKUP("P004", A2:A6, D2:D6, "Not found")
OUTPUT
18500
💡

XLOOKUP separates the lookup column from the return column — no col_index counting. It also looks to the left, which VLOOKUP cannot do.

📌 Key Points to Remember

  • ✓VLOOKUP always searches the leftmost column of the table_array — restructure your table if the key is not on the left
  • ✓Use FALSE as the fourth argument for exact matching — TRUE is only for numeric range lookups (like tax brackets)
  • ✓XLOOKUP can return multiple columns at once and search from the bottom up
  • ✓IFERROR wraps any function: =IFERROR(formula, "fallback value")
  • ✓For very large datasets (100k+ rows), INDEX-MATCH is faster than VLOOKUP

🏢 Real-World Application

Operations analysts use VLOOKUP or XLOOKUP to merge order data with product master data — looking up product names, prices, and categories for each order ID. HR teams look up employee grade and designation from a master employee table to populate payroll sheets. This is Excel equivalent of a SQL JOIN.

⚠️ Common Mistakes to Avoid

WRONGUsing TRUE (or omitting the fourth argument) for exact data lookups
FIXAlways write FALSE as the fourth argument. TRUE causes Excel to return the nearest match, which silently gives wrong answers when data is not perfectly sorted.
WRONGCounting columns wrong for col_index_num
FIXCount from the first column of the table_array, not from column A of the sheet. If your table starts at C2, col_index 1 = column C, col_index 2 = column D, etc.
WRONGVLOOKUP returns #N/A for a value that looks correct
FIXUsually a data type mismatch — the lookup value is a number but the table stores it as text (or vice versa). Use VALUE() to convert text to numbers or TEXT() to convert the other way.
✏️Test Yourself

What does the third argument in =VLOOKUP("P002", A1:E5, 4, FALSE) return?

❓ Frequently Asked Questions

Should I learn VLOOKUP or XLOOKUP?

Learn both. VLOOKUP is asked in almost every analyst interview and is on millions of existing spreadsheets. XLOOKUP is what you should use for new work if your Excel version supports it.

Why does VLOOKUP return the wrong value when data looks correct?

Almost always a data type issue — numbers stored as text, or extra spaces. Use TRIM() to remove spaces and VALUE() to convert text-numbers. You can also check by pressing Ctrl+1 on a cell and reading the Format tab.

Can VLOOKUP return multiple columns?

Standard VLOOKUP returns one column at a time. XLOOKUP can return a whole range. Alternatively, use multiple VLOOKUP formulas with different col_index values, or use INDEX-MATCH.

✏️ Practice Exercise

Create a two-sheet workbook. On Sheet1 (Products), put a table with Product ID, Name, Category, and Price for 8 products. On Sheet2 (Orders), put 10 order rows with a Product ID column. In the next columns on Sheet2, use VLOOKUP or XLOOKUP to pull the Name and Price for each order automatically from Sheet1.

← PreviousIF & IFS FunctionsNext →INDEX & MATCH
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.