Lookup Functions: VLOOKUP, INDEX-MATCH, and XLOOKUP

The Two Spreadsheets That Wouldn’t Talk

The sales team had customer orders in one spreadsheet and product prices in another. Every month, an intern manually looked up each product, found its price, and typed it into the orders sheet. Three hundred products. One thousand orders. Forty hours of error-prone manual work.

One VLOOKUP formula turned forty hours into four seconds.

By the end of this lesson, you’ll connect data across any spreadsheet using the three essential lookup functions—and know exactly when to use each one.

🔄 Quick Recall: In the previous lesson, we organized data into proper flat tables with consistent formatting. Remember why structured data matters? Lookup functions depend on clean, consistent data. A product called “Widget” won’t match “Widget " (with a trailing space). Good structure from Lesson 3 makes Lesson 4 work flawlessly.

VLOOKUP: The Classic Lookup

VLOOKUP searches for a value in the leftmost column of a range and returns a value from a specified column.

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

Example: Look up a product ID and return its price: =VLOOKUP(A2, Products!A:C, 3, FALSE)

• A2 — The value to search for (product ID)
• Products!A:C — The table to search in (columns A through C on the Products sheet)
• 3 — Return the value from the 3rd column (price)
• FALSE — Exact match (almost always use FALSE)

Common VLOOKUP mistakes:

✅ Quick Check: You have a VLOOKUP formula that returns #N/A even though you can see the value exists in the table. What are the three most likely causes?

INDEX-MATCH: The Flexible Powerhouse

INDEX-MATCH is two functions working together:

MATCH finds the position (row number) of a value in a range. INDEX returns the value at a specific position in a range.

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

Example: Same lookup as the VLOOKUP above: =INDEX(Products!C:C, MATCH(A2, Products!A:A, 0))

• Products!C:C — The column to return values from (price)
• A2 — The value to search for (product ID)
• Products!A:A — The column to search in
• 0 — Exact match

Why INDEX-MATCH Is Better

1. Looks in any direction. VLOOKUP can only look right. INDEX-MATCH looks left, right, or in a completely different table.
2. Doesn’t break when columns change. VLOOKUP’s column number breaks when you insert a column. INDEX-MATCH uses column references.
3. Better performance. On large datasets (100K+ rows), INDEX-MATCH is faster.
4. More readable. You can see which column is being returned.

I need to look up data between two tables:

Table 1 (Orders): Columns are Order ID, Product ID,
Quantity, Customer
Table 2 (Products): Columns are Product ID, Name,
Price, Category

I want to bring the product Name and Price into
the Orders table.

Write both VLOOKUP and INDEX-MATCH versions.
Explain which is better for this situation and why.

XLOOKUP: The Modern Solution

XLOOKUP is available in Excel 365 and Google Sheets. It’s designed to replace both VLOOKUP and INDEX-MATCH with cleaner syntax.

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

Example: =XLOOKUP(A2, Products!A:A, Products!C:C, "Not found")

• A2 — What to search for
• Products!A:A — Where to search
• Products!C:C — What to return
• "Not found" — What to show if there’s no match (built-in error handling)

XLOOKUP advantages:

• Simplest syntax of the three
• Built-in error handling (no need for IFERROR wrapper)
• Looks in any direction
• Supports exact match, approximate match, and wildcard matching

✅ Quick Check: If you’re building a spreadsheet that will be used by others who might have older versions of Excel, should you use XLOOKUP? What’s the safe alternative?

When to Use Which

Multi-Criteria Lookups

Sometimes you need to look up based on two or more criteria. For example: find the price for Product “Widget” in Region “North.”

Method 1: Concatenate helper column

Add a helper column that combines criteria: =A2&B2 creates “WidgetNorth” Then VLOOKUP against the helper column.

Method 2: INDEX-MATCH with multiple criteria

=INDEX(C:C, MATCH(1, (A:A="Widget")*(B:B="North"), 0))

Note: This is an array formula. In Excel, press Ctrl+Shift+Enter. In Google Sheets, it works normally.

I need a lookup with multiple criteria:
- Table has columns: [list columns]
- I need to match on: [list criteria columns]
- I need to return: [which column]

Write the formula using:
1. Helper column approach (simpler)
2. Array formula approach (no helper needed)
Explain the trade-offs.

Try It Yourself

Create two sheets in a spreadsheet:

Sheet 1 - Orders: Order ID, Product ID, Quantity Sheet 2 - Products: Product ID, Product Name, Unit Price

Add 10 products and 20 orders. Then:

1. Use VLOOKUP to bring Product Name into the Orders sheet
2. Use INDEX-MATCH to bring Unit Price into the Orders sheet
3. Add a calculated column: Quantity * Unit Price = Total
4. Try XLOOKUP if your spreadsheet supports it
5. Test what happens when a Product ID doesn’t exist in the Products table

Key Takeaways

• VLOOKUP is the simplest lookup but can only look right and breaks when columns are inserted
• INDEX-MATCH is more flexible, robust, and performant—the professional’s choice for production spreadsheets
• XLOOKUP offers the best of both worlds with cleaner syntax but requires Excel 365 or Google Sheets
• Always use FALSE (exact match) in VLOOKUP unless you specifically need approximate matching
• Wrap lookups in IFERROR to handle missing values gracefully
• AI can write any lookup formula from a plain English description of your data structure

Up Next

In Lesson 5: Pivot Tables, we’ll learn the single most powerful feature in spreadsheets—turning thousands of rows into actionable summaries with a few clicks.