VLOOKUP vs XLOOKUP vs INDEX MATCH: Simple Examples

Sample Dataset

All examples in this guide use the following product table. Imagine this is your reference table on a sheet called Products:

Your task: a user types a Product Code in cell H2. You need formulas that return the Product Name, Category, Price and Stock for that code.

VLOOKUP: Syntax, Example and Limitations

Syntax

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

• lookup_value — the value to find (e.g., the product code in H2)
• table_array — the range that contains both the key column and the return column
• col_index_num — which column number in the range to return (1 = first, 2 = second, etc.)
• range_lookup — FALSE for exact match, TRUE for approximate match. Always use FALSE.

Examples Using the Sample Dataset

Assume the Products table is in cells A1:E7 (header row + 6 data rows):

=VLOOKUP(H2, $A$2:$E$7, 2, FALSE)   → Returns Product Name
=VLOOKUP(H2, $A$2:$E$7, 3, FALSE)   → Returns Category
=VLOOKUP(H2, $A$2:$E$7, 4, FALSE)   → Returns Price
=VLOOKUP(H2, $A$2:$E$7, 5, FALSE)   → Returns Stock

If H2 = "P003", these four formulas return: Notebook A5, Stationery, 45, 500.

Limitations of VLOOKUP

• Lookup column must be leftmost. VLOOKUP can only look left-to-right. If you want to find the Product Code by searching the Product Name (column B), VLOOKUP cannot do it — the search column must always be column A of your selected range.
• Column number is fragile. If you insert a new column between B and C in your Products table, all your VLOOKUP formulas with col_index_num = 3 break silently — they now return the wrong column without showing an error.
• No built-in error handling. If H2 contains "P999" (a code that does not exist), VLOOKUP returns #N/A. You must wrap it in IFERROR manually.
• Finds first match only. If the lookup value appears more than once, VLOOKUP always returns the value from the first match — it cannot return all matches.

INDEX MATCH: Two Functions, One Powerful Lookup

How It Works

INDEX MATCH is not a single function — it is a combination. MATCH finds the row number. INDEX uses that row number to return a value from any column you choose.

MATCH(lookup_value, lookup_range, 0)  → Returns the ROW number (position)
INDEX(return_range, row_number)       → Returns the VALUE at that row

Syntax Combined

=INDEX(return_column, MATCH(lookup_value, lookup_column, 0))

Examples Using the Sample Dataset

=INDEX($B$2:$B$7, MATCH(H2, $A$2:$A$7, 0))   → Product Name
=INDEX($C$2:$C$7, MATCH(H2, $A$2:$A$7, 0))   → Category
=INDEX($D$2:$D$7, MATCH(H2, $A$2:$A$7, 0))   → Price
=INDEX($E$2:$E$7, MATCH(H2, $A$2:$A$7, 0))   → Stock

Advantages Over VLOOKUP

• Can look in any direction. The lookup column and return column are specified separately — you can look right, left, or in any order.
• Inserting columns does not break it. Because you reference the return column by range (e.g., $D$2:$D$7) rather than a number, inserting a new column simply shifts the reference automatically.
• Works in all Excel versions. INDEX and MATCH have been available since Excel 2003. Ideal for files shared across different Excel versions.
• Two-way lookup. Use two MATCH functions — one for row, one for column — to look up both a row header and a column header simultaneously.

Two-Way Lookup Example

Suppose you have a table of regional monthly targets with months as column headers and regions as row headers. Find the target for "North" in "March":

=INDEX(B2:M10, MATCH("North", A2:A10, 0), MATCH("March", B1:M1, 0))

XLOOKUP: The Modern Replacement

Syntax

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

• lookup_value — what to find
• lookup_array — where to find it (a single column or row)
• return_array — what to return (a single column, row, or multiple columns)
• if_not_found — what to show if no match (built-in error handling — no IFERROR needed)
• match_mode — 0 = exact match (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard
• search_mode — 1 = first to last (default), -1 = last to first, 2 = binary search ascending, -2 = binary search descending

Examples Using the Sample Dataset

=XLOOKUP(H2, $A$2:$A$7, $B$2:$B$7, "Not found")   → Product Name
=XLOOKUP(H2, $A$2:$A$7, $C$2:$C$7, "Not found")   → Category
=XLOOKUP(H2, $A$2:$A$7, $D$2:$D$7, "Not found")   → Price
=XLOOKUP(H2, $A$2:$A$7, $E$2:$E$7, "Not found")   → Stock

Return Multiple Columns in One Formula

XLOOKUP can return an entire row — multiple columns at once — with a single formula:

=XLOOKUP(H2, $A$2:$A$7, $B$2:$E$7, "Not found")

This single formula spills results across four cells: Product Name, Category, Price and Stock. No need to write four separate formulas.

Version Compatibility Note

Error Handling Comparison

If the lookup value does not exist in the table:

Full Comparison Table

When to Use Which

• Use XLOOKUP when you are on Microsoft 365 or Excel 2021+. It handles more scenarios with less code. The built-in error handling alone makes it worth using over VLOOKUP.
• Use INDEX MATCH when: (1) the file will be opened on Excel 2016 or 2019, (2) you need a two-way lookup using row and column headers, or (3) you need a left-column lookup in a version without XLOOKUP.
• Use VLOOKUP when: (1) the workbook must work on Excel 2013 or earlier, (2) you are in an environment that has standardised on VLOOKUP and changing conventions would cause confusion, or (3) the interview specifically asks you to demonstrate VLOOKUP.

Trainer's Practical Advice

Common Mistakes

3 Practice Questions

Question 1

You have a customer table where Customer ID is in column B and Customer Name is in column A. You want to look up a Customer ID (in cell F2) and return the Customer Name. Which function can do this most easily and why?

Answer: XLOOKUP or INDEX MATCH — because the return column (A) is to the LEFT of the lookup column (B). VLOOKUP cannot look left. XLOOKUP formula: =XLOOKUP(F2, $B$2:$B$100, $A$2:$A$100, "Not found"). INDEX MATCH formula: =INDEX($A$2:$A$100, MATCH(F2, $B$2:$B$100, 0))

Question 2

Your VLOOKUP formula is returning #N/A even though you can see the matching value in the table. The lookup column contains employee codes like "EMP001". What are the three most likely causes?

Answer: (1) Extra spaces in the table or lookup value — use TRIM(). (2) Data type mismatch — the lookup value is a number but the table column stores text (or vice versa). (3) The 4th argument (range_lookup) is omitted or TRUE — in sorted data this can return unexpected approximate matches.

Question 3

You need to find the sales figure for "South" region in "Q2" from a cross-tab table. The regions run down the rows (column A) and quarters run across the columns (row 1). Write the formula.

Answer: Use INDEX MATCH with two MATCH functions: =INDEX(B2:E10, MATCH("South", A2:A10, 0), MATCH("Q2", B1:E1, 0)). Alternatively: =XLOOKUP("South", A2:A10, XLOOKUP("Q2", B1:E1, B2:E10))

Frequently Asked Questions