Blog
Master Excel Formulas | How to Use XLOOKUP, VLOOKUP & INDEX MATCH
Introduction
Welcome to Part 6 of our 10-part Excel Tips & Tricks Series. If you’ve been following from the start, you’ve already learned how to navigate Excel efficiently, write basic formulas, clean data with logic functions, and organize your work using Tables.
If you’ve ever tried matching data across two spreadsheets, you know it can be time-consuming and error-prone. Scrolling through thousands of rows just to find a value isn’t practical. In this guide, we’ll show you how to use Excel lookup functions—VLOOKUP, XLOOKUP, and INDEX MATCH—to find and connect data instantly. Plus, we provide clear examples you can copy and use immediately.
For optimal performance, especially with large datasets and modern functions like XLOOKUP, many professionals use Microsoft Office 2024 LTSC Professional Plus, available at esoftwarestore.
Excel Lookup Functions Cheat Sheet – Quick Summary
| Function | Primary Use |
|---|---|
| VLOOKUP | Finds data vertically (older method) |
| HLOOKUP | Finds data horizontally |
| XLOOKUP | Modern, flexible lookup (recommended) |
| INDEX + MATCH | Advanced, flexible alternative |
What Are Excel Lookup Functions?
Excel lookup functions allow you to find a value in one table and return related data from another. Whether matching product IDs, employee records, or pricing data, lookup functions are the fastest way to get results without manually searching through rows.
How to Use VLOOKUP in Excel (Step-by-Step)
VLOOKUP (Vertical Lookup) has been a staple in Excel for decades. Many corporate workbooks still rely on it heavily.
What is VLOOKUP?
VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row.
Syntax:
- lookup_value: The value you are searching for (e.g., Product ID “A101”)
- table_array: The range where data is stored (e.g., A1:D500)
- col_index_num: The column number to return the result from (e.g., 3)
- range_lookup: Always type FALSE for exact match
Example:
Limitations of VLOOKUP
- Cannot search to the left
- Breaks if columns are inserted
- Requires manual column indexing
Pro Tip: Most modern workflows now use XLOOKUP to avoid these limitations.
How to Use HLOOKUP in Excel
HLOOKUP (Horizontal Lookup) searches for a value in the top row of a table and returns a value from a specified row below. While most Excel data is vertical, HLOOKUP is useful for horizontal datasets.
Syntax:
Limitations of HLOOKUP
- Only works horizontally
- Requires exact row indexing
- Less flexible than modern functions
Pro Tip: Use XLOOKUP for modern Excel or INDEX MATCH for legacy workbooks.
What Is XLOOKUP in Excel? (Modern Alternative)
XLOOKUP is the modern, flexible replacement for VLOOKUP and HLOOKUP. It works left or right, does not break when columns are inserted, and includes built-in error handling.
Syntax:
Parameters:
- lookup_value: Value to find
- lookup_array: Range of IDs (e.g., A1:A500)
- return_array: Range with the result (e.g., C1:C500)
- if_not_found: Optional – what to return if not found
- match_mode: Defaults to exact match
Why XLOOKUP Is Better
- Works left and right
- Does not break when columns change
- Has built-in error handling
- Defaults to exact match
XLOOKUP vs VLOOKUP – Quick Comparison
| Feature | VLOOKUP | XLOOKUP |
|---|---|---|
| Direction | Right only | Any direction |
| Flexible | No | Yes |
| Breaks easily | Yes | No |
| Recommended | ❌ | ✅ |
How to Use INDEX MATCH in Excel
Before XLOOKUP, INDEX + MATCH was the go-to for complex lookups. It’s still valuable for older spreadsheets and two-dimensional searches.
How INDEX MATCH Works
- MATCH: Finds the row number of a value
- INDEX: Returns the value at a specific row/column
Syntax:
How to Lookup Data Between Sheets in Excel
Example Scenario: You have Sheet 1 (Sales Report) with 5,000 orders but no prices, and Sheet 2 (Master Catalog) with product IDs and prices. You need to pull prices into Sheet 1.
Using XLOOKUP (Modern Way)
- Go to the Price column in Sheet 1.
- Enter the formula:
- Drag down the fill handle to populate all rows instantly.
Pro Tips for Accurate Lookups
- Avoid VLOOKUP-heavy workbooks – large formulas can slow Excel
- Check for hidden spaces using the TRIM function
- Use named ranges for easier formula management
Example:
Excel Tips & Tricks Series (10-Part Guide)
- Part 1: Excel Tips for Beginners
- Part 2: 15 Excel Keyboard Shortcuts
- Part 3: Excel Formulas Made Simple
- Part 4: Logical and Text Functions in Excel
- Part 5: Sorting, Filtering, and Tables in Excel
- Part 6: Excel Lookup Functions Explained (this article)
- Part 7: Creating Charts and Dashboards – Coming Soon
- Part 8: How to Use PivotTables in Excel
- Part 9: Advanced Excel Formulas for Power Users
- Part 10: Excel Automation and Best Practices
Final Thoughts
Lookup functions are the bridge connecting different datasets. Without them, spreadsheets become messy and difficult to manage. With XLOOKUP, VLOOKUP, or INDEX MATCH, your data becomes clean, connected, and efficient. Mastering XLOOKUP is one of the best ways to boost your Excel skills this year.
FAQ
Q1: What is the best lookup function in Excel?
A: XLOOKUP – flexible, reliable, and includes error handling.
Q2: What is the difference between XLOOKUP and VLOOKUP?
A: XLOOKUP works left and right, doesn’t break with column changes, and has built-in error handling.
Q3: How do I lookup data from another sheet in Excel?
A: Use XLOOKUP or VLOOKUP with sheet references:
Q4: Why is my Excel lookup not working?
A: Common causes: extra spaces, wrong match type, missing values. Use TRIM and exact match.
Q5: When should I use INDEX MATCH instead of XLOOKUP?
A: For older Excel versions or complex two-dimensional searches.