Lesson 3 of 8 · 7 min · Formulas

XLOOKUP, VLOOKUP and INDEX/MATCH

Covers: Microsoft 365 Excel. Updated 13 Nov 2026. Part of the Data Analyst roadmap.

What you will learn

  • Look up values with XLOOKUP
  • Handle "not found" and return several columns
  • Understand VLOOKUP, its limits and the FALSE trap
  • Use INDEX and MATCH as a flexible alternative
  • Use approximate match for grade bands and tax slabs

In simple words

A lookup is like using a phone book: you know the name, and you want the phone number next to it. In Excel you have one table with employee ids and another with ids and names. A lookup takes an id from the first table, finds it in the second, and brings back the name. It saves you from copying and pasting by hand, and it stays correct when data changes.

Words to know

  • Lookup value: the thing you are searching for, such as an id.
  • Lookup array: the column where Excel searches.
  • Return array: the column that holds the answer.
  • Exact match: finding a value that is identical to the lookup value.

The tables

Orders (in a Table named Orders) have ProductID but no product name. A second Table named Products has ProductID, Name and Price.

XLOOKUP

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

In the Orders table, add a column for the name:

=XLOOKUP([@ProductID], Products[ProductID], Products[Name], "Not found")

Excel finds the ProductID in the Products column and returns the name from the same row. Why it is better than VLOOKUP:

  • It looks left or right; the lookup column does not have to be first.
  • Exact match is the default (no FALSE to forget).
  • It has a built-in "not found" argument instead of needing IFERROR.
  • It returns several columns at once: =XLOOKUP(A2, Products[ProductID], Products[[Name]:[Price]]) spills name and price into two cells.
  • It can search from the bottom (search_mode of -1) to find the last match.

XLOOKUP needs Microsoft 365, Excel 2021 or later. In older files, use VLOOKUP or INDEX/MATCH.

Approximate match for bands

Match modes: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard. For a grade table sorted by lower bound:

Score from Grade
0 F
40 D
60 C
75 B
90 A
=XLOOKUP(B2, Grades[From], Grades[Grade], , -1)

A score of 82 returns B (the next smaller bound is 75). The two commas leave if_not_found empty. This beats a long nested IF because you change the table, not the formula. The same pattern works for tax slabs, commission rates and shipping bands.

VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP(A2, Products!A:C, 2, FALSE)
  • The lookup value must be in the first column of table_array.
  • col_index_num counts columns from the left of that range (2 means the second column).
  • The last argument matters: FALSE means exact match. If you leave it out, it defaults to TRUE (approximate), which on unsorted data returns wrong answers without any error. Always type FALSE for exact lookups.
  • Inserting a column in the table can silently change which column the number 2 points to.
  • It cannot look to the left.

You will still meet VLOOKUP in old workbooks and interviews, so know it, but prefer XLOOKUP for new work.

INDEX and MATCH

Two functions that work together and run in every Excel version:

  • MATCH(value, range, 0) returns the position of the value in a range (0 means exact).
  • INDEX(range, position) returns the item at that position.
=INDEX(Products[Name], MATCH([@ProductID], Products[ProductID], 0))

It looks left or right and is not broken by inserted columns. Two-way lookup (row and column): =INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0)) returns the value where a chosen row and column meet.

Lookup problems and fixes

Symptom Likely cause Fix
#N/A although the value is visible number stored as text in one column and number in the other, or trailing spaces convert types; use TRIM; check with =ISNUMBER(cell)
Wrong values from VLOOKUP approximate match used add FALSE
#REF! col_index_num larger than the table width count columns or use XLOOKUP
Duplicates in the lookup column lookup returns only the first match clean keys, or use a pivot or FILTER
Slow workbook thousands of lookups on whole columns in old Excel use Tables or exact-sized ranges

Lookups from another file or sheet

Reference another sheet with Products!A:C or a Table name. Linking to a closed workbook creates an external link that breaks if the file moves; consider importing the data with Power Query (Get Data) instead.

Common mistakes

  • Forgetting FALSE in VLOOKUP.
  • Mismatched data types or hidden spaces causing #N/A.
  • A col_index_num that breaks when columns are inserted.
  • Looking up non-unique keys and trusting the first match.
  • Typing the lookup value instead of referencing a cell.

Try it yourself

  1. Create Products and Orders tables and add a product name to each order with XLOOKUP.
  2. Add price and compute the line total.
  3. Make a grade table and use approximate match to grade marks.
  4. Rewrite your lookup with INDEX/MATCH.
  5. Break a lookup on purpose with a trailing space, then fix it with TRIM.

Check yourself

What advantages does XLOOKUP have over VLOOKUP?

It can look left, defaults to exact match, has a "not found" argument, returns several columns, and does not break when columns are inserted.

What does the last argument of VLOOKUP do?

FALSE requests an exact match; TRUE (the default) requests an approximate match on sorted data.

What does MATCH(x, range, 0) return?

The position of x within the range, for an exact match.

Why does a lookup return #N/A even though the value looks present?

Usually a type mismatch (text versus number) or hidden spaces.

Interview questions

How would you do a lookup that returns a value to the left of the key?

XLOOKUP, or INDEX/MATCH; VLOOKUP cannot look left.

Explain approximate match with an example.

It finds the largest lookup value that is less than or equal to your value in a sorted table, such as a score of 82 in bands 0, 40, 60, 75, 90, which returns the 75 band.

How do you look up on two criteria?

Join the criteria with & in both the lookup value and a helper column, or use XLOOKUP with a boolean array, =XLOOKUP(1, (A:A=x)*(B:B=y), C:C), or FILTER.

Summary

  • XLOOKUP(value, lookup_column, return_column, not_found) is the modern, safe lookup.
  • Approximate match with a band table replaces long nested IFs.
  • VLOOKUP needs FALSE and the key in the first column; INDEX/MATCH works everywhere.
  • Most #N/A problems are mismatched types or stray spaces.

Going deeper

See how much of this you can explain

A free mock interview asks you new questions on Excel for analysts for Data Analyst and scores every answer, with what to fix.