VLOOKUP and XLOOKUP Explained With Real Data

What You Will Learn

• What VLOOKUP does and why analysts love it
• The exact syntax with a worked example
• The five most common VLOOKUP mistakes
• How XLOOKUP fixes VLOOKUP's biggest limitations

Why This Topic Matters

Real data rarely lives in one table. Customer names are in one sheet, order amounts in another, product categories in a third. To analyze them together, you need to join the tables. VLOOKUP is the spreadsheet way to do this. It is the single most useful formula in business Excel, and the most-misused one. Get it right and your reports build themselves; get it wrong and you silently enrich the wrong customer.

The Problem VLOOKUP Solves

You have two tables:

Table 1 — Orders (Sheet "Orders")

Table 2 — Customers (Sheet "Customers")

You want a single view: order_id, customer name, city, amount. The customer_id links the two tables. VLOOKUP looks up that ID in Table 2 and brings back the matching name and city.

VLOOKUP Syntax

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

Worked Example

In the Orders sheet, in cell D2 (next to the first order), type:

=VLOOKUP(B2, Customers!A:C, 2, FALSE)

Line by line:

• B2 — the customer_id we want to look up ("C12").
• Customers!A:C — the lookup table on the Customers sheet, columns A through C.
• 2 — return the value from the 2nd column of that range (the "name" column).
• FALSE — require an exact match on customer_id.

Result: "Anita". Drag the formula down to fill D3, D4. D3 looks up "C07" → "Ravi". D4 looks up "C12" → "Anita" again.

Repeat for city in column E:

=VLOOKUP(B2, Customers!A:C, 3, FALSE)   → "Mumbai"

Final joined view

This is exactly what SQL does with a JOIN — see lesson 22 for the SQL version of the same operation.

The Five Most Common VLOOKUP Mistakes

1. Forgetting FALSE

If you omit the last argument, Excel uses TRUE (approximate match). This silently returns wrong values when there is no exact match. Always use FALSE.

2. Looking up to the left

VLOOKUP can only look up a value in the first column of the table and return a value to the right. If your lookup column is on the right of the value you want to return, VLOOKUP cannot do it. Reorder the columns or use XLOOKUP/INDEX-MATCH.

3. Not using absolute references for the table

When you drag the formula down, the table range shifts. To prevent this, lock it with $: =VLOOKUP(B2, Customers!$A$1:$C$100, 2, FALSE). Or use a whole-column reference like Customers!A:C.

4. Counting the wrong column index

The col_index_num is relative to the start of your table_array, not the column letter in the sheet. If your table_array starts at column B, then col 1 = B, col 2 = C, col 3 = D — even though column letters shifted.

5. Not handling missing matches

If the lookup_value is not in the table, VLOOKUP returns #N/A. Wrap in IFERROR for a cleaner result:

=IFERROR(VLOOKUP(B2, Customers!A:C, 2, FALSE), "unknown")

XLOOKUP — The Modern Replacement

XLOOKUP is available in Excel 365, Excel 2021+, and Google Sheets. It fixes VLOOKUP's limitations:

Syntax

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

Worked example — same lookup as before

=XLOOKUP(B2, Customers!A:A, Customers!B:B, "unknown")

Line by line:

• B2 — what to find.
• Customers!A:A — where to look for it.
• Customers!B:B — what to return from the matching row.
• "unknown" — what to return if no match (built-in, no IFERROR needed).

Advantages of XLOOKUP

• Can look up to the left. lookup_array and return_array are independent.
• No column counting. You specify the return column directly.
• Built-in error handling. The 4th argument replaces IFERROR.
• Defaults to exact match. No more forgetting FALSE.

When to Use What

Common Mistakes

1. Forgetting FALSE in VLOOKUP. The #1 mistake. Always FALSE for exact match.
2. Shifting table references when dragging down. Use $ or whole-column references.
3. Mismatched data types. If customer_id is text in one table and a number in another, VLOOKUP returns #N/A even when values look identical.
4. Lookup table has duplicate IDs. VLOOKUP returns the first match only. Clean duplicates first.
5. Not handling #N/A. Wrap in IFERROR (VLOOKUP) or use the 4th argument (XLOOKUP).

Practical Exercise (15 minutes)

1. Create the two tables above in separate sheets of a new spreadsheet.
2. In the Orders sheet, add a "name" column. Use VLOOKUP to populate it from Customers.
3. Add a "city" column. Use VLOOKUP again.
4. If you have Excel 365 or Google Sheets, redo the same with XLOOKUP.
5. Add a fake customer_id "C99" to one order. Confirm VLOOKUP returns #N/A, then wrap it with IFERROR to return "unknown".

Mini Challenge

Find any two related datasets — e.g., a list of products and a list of orders containing product IDs. Use VLOOKUP (or XLOOKUP) to enrich the orders with product names. Then use SUMIF to compute total revenue by product. You have just done a manual JOIN + GROUP BY in spreadsheet form — the exact pipeline we will automate in SQL in lesson 22.

Key Takeaways