How to Use XLOOKUP, VLOOKUP & INDEX MATCH in Excel

Welcome to Part 6 of our 10-part Excel Tips & Tricks series. If you have been following the series from the beginning, you have already learned how to navigate quickly, write basic formulas, clean your data with logic, and organize your work using Tables.

If you’ve ever tried to match data across two spreadsheets, you know how slow and error-prone it can be. Scrolling through thousands of rows to find a value isn’t practical. In this guide, you’ll learn how to use Excel lookup functions to find and connect data instantly. We’ll cover VLOOKUP, XLOOKUP, and INDEX MATCH, with clear examples you can copy and use right away.

For optimal performance when working with large datasets and modern functions such as XLOOKUP, many professionals use Microsoft Office 2024 LTSC Professional Plus.

Excel Lookup Functions Cheat Sheet (Quick Summary)

What Are Excel Lookup Functions?

Excel lookup functions help you find a value in one table and return related data from another table. If you need to match product IDs, employee records, or pricing data across sheets, lookup functions are the fastest way to do it.

How to Use VLOOKUP in Excel (With Example)

For decades, VLOOKUP (Vertical Lookup) has been the most famous function in Excel. If you are entering a corporate environment, you will likely encounter workbooks that rely heavily on it.

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:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• Lookup_value: What are you looking for? (e.g., Product ID "A101")
• Table_array: Where is the data stored? (e.g., A1:D500)
• Col_index_num: Which column has the answer? (If your price is in the 3rd column, you type 3).
• Range_lookup: Important! Always type FALSE here for an "Exact Match". If you leave it blank or type TRUE, Excel might give you the wrong answer if the list isn't sorted perfectly.

Example:
=VLOOKUP(A2, A1:D500, 3, FALSE)
This looks for the value in A2 and returns the value from the 3rd column.

Limitations of VLOOKUP

• Can only look to the right
• Breaks if columns are inserted
• Requires manual column indexing

👉 Because of these limitations, most modern workflows now use XLOOKUP.

How to Use HLOOKUP in Excel (Horizontal Lookup Explained)

What is HLOOKUP?

HLOOKUP (Horizontal Lookup) searches for a value in the top row of a table and returns a value from a specified row below it. While most Excel data is structured vertically (columns), HLOOKUP is useful when your data is arranged horizontally.

Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Example:
=HLOOKUP("Jan", A1:F5, 2, FALSE)

Limitations of HLOOKUP

• Only works horizontally (less common structure)
• Requires exact row indexing (can break easily)
• Less flexible compared to modern functions

👉 Because of these limitations, most users now prefer XLOOKUP, which works in any direction and is easier to maintain.

Quick Tip: If you’re unsure whether to use HLOOKUP or not, use XLOOKUP for modern Excel or INDEX MATCH for older versions.

What Is XLOOKUP in Excel? (Modern Alternative)

XLOOKUP is a modern Excel function that searches for a value in one range and returns a result from another range, without the limitations of VLOOKUP.

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

• Lookup_value: What are you looking for?
• Lookup_array: Where is the list of IDs? (e.g., A1:A500)
• Return_array: Where is the list of answers? (e.g., C1:C500)
• If_not_found: (Optional) You can tell Excel what to say if the ID doesn't exist (e.g., "Item Missing"). No more #N/A errors!
• Match_mode: XLOOKUP defaults to an Exact Match, so you don't have to worry about the "FALSE" argument anymore.

Why XLOOKUP is Better:

• Works left and right
• Doesn’t break when columns change
• Built-in error handling
• Defaults to exact match

XLOOKUP vs VLOOKUP (Quick Comparison)

How to Use INDEX MATCH in Excel

Before XLOOKUP existed, power users used a combination of the INDEX and MATCH functions. While XLOOKUP has replaced this for many, you still need to know how it works if you are working on older spreadsheets or legacy systems.

• MATCH: Tells Excel where a value is (e.g., "Product A101 is in Row 15").
• INDEX: Tells Excel what is in a specific location (e.g., "Tell me what is in Row 15 of the Price column").

Syntax:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

This method is still useful for older Excel versions, complex lookups, and two-dimensional searches.

How to Lookup Data Between Sheets in Excel

Real-world scenario: You have a Sheet 1 (Sales Report) with 5,000 orders but no prices, and a Sheet 2 (Master Catalog) with all Product IDs and prices. You need to pull the price into Sheet 1 to calculate revenue.

Using XLOOKUP (The Modern Way):

1. In your Sales Report (Sheet 1), go to the "Price" column.
2. Type: =XLOOKUP(A2, 'Master Catalog'!A:A, 'Master Catalog'!B:B, 0)

• A2: The Product ID you want to find.
• 'Master Catalog'!A:A: Where IDs are listed in the catalog.
• 'Master Catalog'!B:B: Where prices are listed.
• 0: Tells Excel to return a zero if the ID isn't found.

Simply double-click the fill handle, and all 5,000 rows will populate instantly.

Pro Tips for Accurate Lookups

• Avoid "VLOOKUP Heavy" Workbooks: Having 100,000 VLOOKUPs can slow down Excel. Convert data to a Table (see Part 5) and use XLOOKUP for better performance.
• Check for Spaces: Hidden spaces (e.g., "A101 ") will cause lookups to fail. Use the TRIM function to clean data first.
• Use Named Ranges: Name Sheet2!$A$2:$B$5000 as "PriceList". Your formula =XLOOKUP(A2, PriceList_IDs, PriceList_Values) becomes much easier to troubleshoot.