Lookup Functions¶

Lookups combine data from different tables — pulling a product name into an orders sheet, matching a customer ID to their details. This is Excel's version of a SQL JOIN, and it's one of the most-tested skills in analyst interviews.

Learning Objectives¶

Use XLOOKUP (the modern standard)
Use VLOOKUP (still common in legacy files)
Use INDEX/MATCH (the flexible classic)
Avoid the common lookup pitfalls

XLOOKUP — The Modern Standard (Excel 365 / 2021+)¶

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

' Find a product name by its ID
=XLOOKUP(A2, Products[ID], Products[Name], "Not found")

' Look up a price
=XLOOKUP(A2, Products[ID], Products[Price], 0)

Why XLOOKUP is the best choice: - Looks up in any direction (left, right, up, down) - Exact match by default (no silent errors) - Built-in not-found handling (no need to wrap in IFERROR) - Returns a direct column reference (robust — doesn't break when columns shift)

VLOOKUP — The Legacy Workhorse¶

You'll still see VLOOKUP everywhere, so you must know it.

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

' Find a product name (3rd column of the Products range), exact match
=VLOOKUP(A2, Products!A:D, 3, FALSE)

Always pass FALSE as the last argument

The 4th argument controls match type. TRUE (or omitted) = approximate match, which silently returns WRONG results if the data isn't sorted. FALSE = exact match. Always use FALSE unless you specifically need approximate matching (like tax brackets).

VLOOKUP only looks RIGHT

VLOOKUP can only return columns to the right of the lookup column. If your ID is in column C and the name is in column A, VLOOKUP can't do it — you need INDEX/MATCH or XLOOKUP.

The col_index_num is fragile

VLOOKUP(A2, B:F, 3, FALSE) returns the 3rd column. If someone inserts a column, the "3rd column" changes and your formula silently returns the wrong data. XLOOKUP and INDEX/MATCH avoid this.

INDEX/MATCH — The Flexible Classic¶

Before XLOOKUP, this was the professional's choice. Still valuable for compatibility and 2D lookups.

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

' Find a product name by ID (works in any direction)
=INDEX(Products[Name], MATCH(A2, Products[ID], 0))

How it works: - MATCH(A2, Products[ID], 0) finds the position of A2 in the ID column (the 0 means exact match) - INDEX(Products[Name], position) returns the name at that position

Two-dimensional lookup (find a value at the intersection of a row and column):

=INDEX(data_grid, MATCH(row_value, row_headers, 0), MATCH(col_value, col_headers, 0))

Which to Use?¶

Situation	Use
Excel 365 / 2021+	XLOOKUP (default choice)
Need to support Excel 2016 or earlier	INDEX/MATCH or VLOOKUP
Lookup column is to the right of return column	XLOOKUP or INDEX/MATCH (not VLOOKUP)
Two-dimensional lookup	INDEX with two MATCH
Inherited a file already using VLOOKUP	VLOOKUP (don't rewrite unnecessarily)

Handling "Not Found"¶

' XLOOKUP — built in
=XLOOKUP(A2, Products[ID], Products[Name], "Not found")

' VLOOKUP / INDEX-MATCH — wrap in IFERROR
=IFERROR(VLOOKUP(A2, Products!A:D, 3, FALSE), "Not found")
=IFERROR(INDEX(Products[Name], MATCH(A2, Products[ID], 0)), "Not found")

Common Pitfalls¶

Number vs text mismatch

If your lookup IDs are stored as text in one sheet ("1001") and numbers in another (1001), the lookup fails with #N/A. Use TEXT() or VALUE() to make them consistent, or clean the data first.

Trailing spaces

"C001 " (with a space) won't match "C001". Wrap with TRIM() if you suspect spaces: XLOOKUP(TRIM(A2), ...).

Duplicate lookup values

Lookups return the FIRST match. If your lookup column has duplicates, you only get the first one. Ensure the lookup key is unique, or you'll get misleading results.

Practice Exercises¶

Warm-up 1. You have an orders sheet with product IDs and a products sheet with IDs and names. Use XLOOKUP to pull the product name into the orders sheet. 2. Do the same with VLOOKUP (remember FALSE). 3. Add a "Not found" fallback for unmatched IDs.

Main 4. Use INDEX/MATCH to look up a customer's city where the ID column is to the RIGHT of the city column (VLOOKUP can't do this). 5. Build a two-way lookup: find revenue at the intersection of a given region (row) and category (column). 6. Handle a case where lookup IDs are text in one sheet and numbers in another.

Stretch 7. Build a small product catalogue lookup tool: type a product ID in one cell, and have name, price, and category all populate automatically using XLOOKUP.

Interview Questions¶

[Beginner] What is the difference between VLOOKUP and XLOOKUP?

[Beginner] Why should you always pass FALSE as the last argument of VLOOKUP?

[Mid-level] When would you use INDEX/MATCH instead of VLOOKUP?

[Mid-level] A VLOOKUP returns #N/A even though you can see the value exists in the data. What are the likely causes?

[Senior] A colleague's workbook breaks every time a column is inserted because of hardcoded VLOOKUP column indexes. How do you make the lookups robust?

Previous: 02-pivot-tables | Next: 04-conditional-formatting