
Picture this: you're working with a spreadsheet containing 500 customer records in one sheet and their purchase history in another. Your boss needs a report showing each customer's total purchases, and they need it in 20 minutes. You know Excel can help, but which lookup function should you use? VLOOKUP has been the go-to for decades, but Microsoft's newer XLOOKUP promises to solve many of VLOOKUP's limitations.
By the end of this lesson, you'll understand both functions inside and out, know exactly when to use each one, and be able to make lookup formulas work reliably in real-world scenarios. More importantly, you'll understand the fundamental differences that make XLOOKUP a game-changer for modern Excel users.
What you'll learn:
You should be comfortable with basic Excel formulas and understand concepts like cell references (A1, B2, etc.) and ranges (A1:C10). If you've never written a formula in Excel before, consider starting with our Excel Fundamentals course first.
You'll need Excel 365 or Excel 2021 to follow along with XLOOKUP examples. VLOOKUP works in all modern versions of Excel.
Before diving into specific functions, let's understand what lookup functions do. Think of a lookup function like searching through an old-fashioned phone book. You know someone's name (the lookup value), and you want to find their phone number (the return value). The phone book is organized alphabetically by name, so you can find what you're looking for.
In Excel, lookup functions work similarly. You provide:
The function searches through the lookup array, finds your value, and returns the corresponding value from the return array.
VLOOKUP (Vertical Lookup) has been Excel's primary lookup function since the 1980s. The "V" stands for vertical because it searches down columns in a table.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Let's break this down with a real example. Imagine you're managing a product database:
| A | B | C | D |
|---|---|---|---|
| Product_ID | Product_Name | Category | Price |
| P001 | Wireless Headphones | Electronics | 89.99 |
| P002 | Coffee Mug | Kitchen | 12.50 |
| P003 | Yoga Mat | Fitness | 35.00 |
| P004 | Bluetooth Speaker | Electronics | 65.99 |
To find the price of product P003, you'd write:
=VLOOKUP("P003", A2:D5, 4, FALSE)
This formula means:
The result would be 35.00.
VLOOKUP searches through the first column of your table array (column A in our example) from top to bottom. When it finds a match for your lookup value, it moves horizontally across that row to the column you specified and returns that value.
Here's what happens step by step:
While VLOOKUP has served Excel users well, it has several significant limitations that can cause real problems in professional settings.
VLOOKUP can only look to the right of your lookup column. In our product table, if you wanted to find a product's ID based on its name, you'd be stuck because Product_Name (column B) is to the right of Product_ID (column A).
=VLOOKUP("Yoga Mat", A2:D5, 1, FALSE) // This won't work!
This fails because VLOOKUP searches in column A but "Yoga Mat" is in column B.
The column index number creates fragile formulas. If someone inserts a new column between Product_Name and Category, your formula breaks:
Original formula: =VLOOKUP("P003", A2:D5, 4, FALSE) returns the price
After inserting a column: The same formula now returns the wrong data because what used to be column 4 is now column 5.
VLOOKUP recalculates the entire table_array for every cell. With a 10,000-row table and 500 lookup formulas, Excel might slow to a crawl.
When VLOOKUP can't find a match, it returns #N/A. While you can wrap it in IFERROR, this adds complexity:
=IFERROR(VLOOKUP("P999", A2:D5, 4, FALSE), "Product not found")
Microsoft introduced XLOOKUP in 2019 to address VLOOKUP's limitations while maintaining familiar functionality. Think of XLOOKUP as VLOOKUP redesigned from the ground up with modern Excel users in mind.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Using the same product database, here's how to find the price of product P003:
=XLOOKUP("P003", A2:A5, D2:D5)
Notice how much cleaner this is:
The result is still 35.00, but the formula is more intuitive and robust.
XLOOKUP can look in any direction. Want to find a product ID based on the product name? No problem:
=XLOOKUP("Yoga Mat", B2:B5, A2:A5)
This searches in the Product_Name column (B2:B5) and returns from the Product_ID column (A2:A5), returning "P003".
Because XLOOKUP uses separate lookup and return arrays, inserting columns won't break your formulas. Each array reference stays correct regardless of structural changes to your spreadsheet.
XLOOKUP includes native error handling with the if_not_found parameter:
=XLOOKUP("P999", A2:A5, D2:D5, "Product not found")
If P999 doesn't exist, the formula returns "Product not found" instead of #N/A.
XLOOKUP is optimized for modern Excel's calculation engine and typically performs better with large datasets.
Let's work through several common business scenarios to see how each function handles them.
You have an employee database and need to find salaries:
| A | B | C | D |
|---|---|---|---|
| Employee_ID | First_Name | Last_Name | Annual_Salary |
| E001 | Sarah | Johnson | 75000 |
| E002 | Mike | Chen | 82000 |
| E003 | Lisa | Rodriguez | 91000 |
Task: Find Sarah Johnson's salary using her Employee ID.
VLOOKUP approach:
=VLOOKUP("E001", A2:D4, 4, FALSE)
XLOOKUP approach:
=XLOOKUP("E001", A2:A4, D2:D4)
Both return 75000, but XLOOKUP is more readable and won't break if you add columns.
Task: Find an Employee ID when you only know the last name "Rodriguez".
VLOOKUP approach: This requires a helper column or complex array formula because you can't look left of the lookup column. You'd need to restructure your data or use INDEX/MATCH:
=INDEX(A2:A4, MATCH("Rodriguez", C2:C4, 0))
XLOOKUP approach:
=XLOOKUP("Rodriguez", C2:C4, A2:A4)
XLOOKUP handles this naturally without additional complexity.
Task: Create a summary that shows an employee's full name and salary.
VLOOKUP approach: Requires multiple formulas:
=VLOOKUP("E002", A2:D4, 2, FALSE) & " " & VLOOKUP("E002", A2:D4, 3, FALSE) // Full name
=VLOOKUP("E002", A2:D4, 4, FALSE) // Salary in another cell
XLOOKUP approach: Can return multiple values in one formula:
=XLOOKUP("E002", A2:A4, B2:D4)
This returns an array containing "Mike", "Chen", and 82000.
Both functions offer sophisticated matching options for different scenarios.
VLOOKUP's fourth parameter controls matching:
For tax bracket calculations with approximate matching:
| A | B |
|---|---|
| Income_Threshold | Tax_Rate |
| 0 | 0.10 |
| 10000 | 0.15 |
| 25000 | 0.22 |
| 50000 | 0.28 |
=VLOOKUP(35000, A2:B5, 2, TRUE)
This returns 0.22 because 35000 falls between 25000 and 50000.
XLOOKUP offers more precise control with match_mode:
Same tax calculation with XLOOKUP:
=XLOOKUP(35000, A2:A5, B2:B5, , -1)
The -1 match_mode finds the next smaller value, returning 0.22.
XLOOKUP's wildcard matching is particularly powerful for partial text matches:
=XLOOKUP("*Johnson*", B2:B4, A2:A4, , 2)
This finds any employee with "Johnson" anywhere in their name.
In real-world applications with large datasets, performance matters significantly.
VLOOKUP evaluates the entire table_array for each lookup, making it slower with:
XLOOKUP typically performs better because:
Pro tip: In Excel 365, consider using dynamic arrays and the FILTER function for complex lookups across very large datasets. Sometimes a completely different approach outperforms traditional lookup functions.
Let's put both functions to work with a realistic business scenario. You're analyzing sales data and need to match customer information across different systems.
Create two tables in Excel:
Table 1: Customer Orders (Sheet1, A1:C6)
| Customer_Code | Order_Date | Order_Amount |
|---|---|---|
| CUST001 | 2024-01-15 | 1250.00 |
| CUST003 | 2024-01-18 | 890.50 |
| CUST002 | 2024-01-20 | 2100.00 |
| CUST001 | 2024-01-22 | 675.00 |
| CUST004 | 2024-01-25 | 1540.00 |
Table 2: Customer Details (Sheet1, E1:G5)
| Customer_Code | Company_Name | Contact_Person |
|---|---|---|
| CUST001 | Tech Solutions Inc | Sarah Miller |
| CUST002 | Global Imports LLC | David Kim |
| CUST003 | Creative Agency | Lisa Wong |
| CUST004 | Manufacturing Pro | James Wilson |
Add Company Names: In column D of the orders table, use both VLOOKUP and XLOOKUP to add company names for each order.
Handle Missing Data: Add a row with Customer_Code "CUST999" to the orders table and make your lookup formulas return "Unknown Customer" instead of an error.
Reverse Lookup: Create a summary showing which customer codes belong to companies with "LLC" in their name.
Task 1 - VLOOKUP solution (cell D2):
=VLOOKUP(A2, $E$2:$G$5, 2, FALSE)
Task 1 - XLOOKUP solution (cell D2):
=XLOOKUP(A2, $E$2:$E$5, $F$2:$F$5)
Task 2 - VLOOKUP with error handling:
=IFERROR(VLOOKUP(A2, $E$2:$G$5, 2, FALSE), "Unknown Customer")
Task 2 - XLOOKUP with error handling:
=XLOOKUP(A2, $E$2:$E$5, $F$2:$F$5, "Unknown Customer")
Task 3 - Reverse lookup (XLOOKUP only):
=XLOOKUP("*LLC*", $F$2:$F$5, $E$2:$E$5, , 2)
Notice how XLOOKUP consistently requires fewer characters and is more readable, especially with error handling and wildcard matching.
The Problem:
=VLOOKUP(A2, E2:G5, 2, FALSE)
When you copy this formula down, the table reference shifts (E3:G6, E4:G7, etc.), causing errors.
The Fix:
=VLOOKUP(A2, $E$2:$G$5, 2, FALSE)
Dollar signs create absolute references that don't change when copied.
XLOOKUP Version:
=XLOOKUP(A2, $E$2:$E$5, $F$2:$F$5)
The Problem: After counting columns manually, you use:
=VLOOKUP("CUST001", E2:G5, 3, FALSE)
But you miscounted and get Contact_Person instead of Company_Name.
Why It Happens: Column counting is error-prone, especially with large tables.
The Fix: Double-check by clicking on your target column and noting its position in the range.
XLOOKUP Advantage: This mistake is impossible with XLOOKUP because you specify the exact return range:
=XLOOKUP("CUST001", E2:E5, F2:F5) // Always returns from column F
The Problem:
=XLOOKUP(A2, E2:E5, F2:G5) // Lookup array has 4 rows, return array has 4 rows but 2 columns
What Happens: XLOOKUP returns multiple values when the return array is wider than one column, which might not be what you want.
The Fix: Ensure your return array matches your intended output:
=XLOOKUP(A2, E2:E5, F2:F5) // Returns single values
=XLOOKUP(A2, E2:E5, F2:G5) // Returns arrays (both company name and contact)
The Problem:
=VLOOKUP("CUST001", E2:G5, 2, TRUE) // Using approximate match with unsorted text data
What Happens: Unpredictable results because approximate match requires sorted data in ascending order.
The Fix: Only use approximate match (TRUE or 1) with sorted numerical data or when you specifically need range matching:
=VLOOKUP("CUST001", E2:G5, 2, FALSE) // Use exact match for text lookups
The Problem: Hard-coding ranges that don't account for new data:
=VLOOKUP(A2, E2:G5, 2, FALSE) // Breaks when row 6 is added
The Fix: Use dynamic ranges or Excel Tables. Convert your lookup data to a table (Ctrl+T), then reference it:
=VLOOKUP(A2, CustomerDetails, 2, FALSE)
Or use dynamic ranges:
=XLOOKUP(A2, E:E, F:F) // Uses entire columns
You now understand the fundamental differences between VLOOKUP and XLOOKUP, and more importantly, when each function shines. VLOOKUP remains valuable for its universal compatibility across Excel versions, but XLOOKUP offers significant advantages in readability, flexibility, and functionality.
Key takeaways:
Both functions follow the same core principle: find a value in one range and return a corresponding value from another range. Understanding this concept deeply will serve you well regardless of which function you choose.
Next steps to build on this foundation:
Master INDEX and MATCH functions - These provide the most flexibility for complex lookups and work in all Excel versions. They're particularly valuable when you need multiple criteria or want to understand the building blocks that XLOOKUP uses internally.
Explore Power Query for data merging - When you're regularly combining data from multiple sources, Power Query's merge operations often outperform lookup functions and provide better data integrity.
Learn dynamic arrays and the FILTER function - Excel 365's dynamic arrays enable powerful data analysis techniques that sometimes eliminate the need for lookup functions entirely, especially when you need to return multiple matching records.
Learning Path: Excel Fundamentals