Excel VLOOKUP, XLOOKUP and INDEX MATCH Explained

A lookup function finds a value in one place, called the key, and returns related information from the same row or column of a table. Give it an employee ID and it brings back the name; give it a mark and it brings back the grade. The main lookup functions in Excel are VLOOKUP, INDEX with MATCH, and the newer XLOOKUP (Microsoft 365 and Excel 2021 or later). They all answer the same question, and the differences are in flexibility and in the mistakes each one invites.

This guide explains each, shows the exact-match and approximate-match modes, the situations where lookups silently return a wrong answer, and how to look up on several conditions. Every result was produced by running the formula in Microsoft Excel.

The sample table

Most examples use this employee table in A1:D6. The ID column is the key. The other columns are what you might want to look up:

VLOOKUP

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) searches for the key in the first column of the table, and returns the value from the column number you give, counted from the left of the table. With FALSE as the last argument it looks for an exact match:

Exact match versus approximate match

The last argument of VLOOKUP is TRUE or FALSE, and if you leave it out it is TRUE. With TRUE Excel does not search for an exact key. It assumes the first column is sorted ascending and uses a fast binary search to find the largest value that does not exceed the key. If the list is not sorted, the answer can be wrong, and there is no error to warn you. Here the same five IDs are stored unsorted, and we ask for IDs that all exist:

Some of those answers are simply wrong, and Excel shows no warning. When the list is sorted, approximate matching behaves, and it returns the largest key that is not bigger than the one you ask for, which is why it is so useful for bands (next section) and so dangerous for exact lookups:

The rule: when you look up an ID, a name or a code, type FALSE.

Approximate match for bands: grades and slabs

Approximate match is the right tool when the key falls between the values in the table: grades, tax slabs, commission tiers, shipping weights. List the lower limit of each band, sorted ascending, and Excel returns the band the key belongs to:

Notice that XLOOKUP makes the intent explicit: its fifth argument, match_mode -1, says “exact match, or the next smaller item”, and the default is exact, so it never surprises you the way VLOOKUP‘s default does.

INDEX and MATCH

MATCH(key, range, 0) returns the position of the key in a range (the 0 means exact match, and it is the safe choice). INDEX(range, position) returns the item at a position. Together they do what VLOOKUP does, but with two separate ranges, so the return column can be anywhere, including to the left of the key:

INDEX can also return a value from a grid, given a row number and a column number, and two MATCH calls can find both. Here rows are products and columns are quarters:

XLOOKUP

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) is the modern replacement (Microsoft 365 and Excel 2021 or later). It is exact by default, the two ranges are separate so it can look in any direction, it has a built-in argument for a not-found message, and it needs no column numbers:

If the return array has several columns, XLOOKUP returns the whole row and spills it across the neighbouring cells:

G2 holds =XLOOKUP(103, A2:A6, B2:D6); the result spills into H2 and I2

Only G2 contains a formula

Several conditions and the last match

Lookups find one key. To look up on two conditions, combine them into one key. Using the sales table from the SUMIFS guide (A1:E11), find the amount for the South region and the Pen product. Join both columns with & and look for the joined text. To find the last match instead of the first, search from the bottom, or use the classic LOOKUP(2, 1/(condition), result) trick:

When there is more than one match, FILTER returns all of them (Microsoft 365):

Why a lookup fails

Most #N/A results for a key that looks present come from four causes: the key is a different type (text versus number), it has a hidden space, the numbers are floating point results, or the lookup range is not locked when the formula is copied. Below, the IDs in A1:A2 are stored as text and Asha in D1 is a clean name:

The sample table for the last two rows has 0.3 in G1. That failure surprises people, because 0.1+0.2=0.3 is TRUE in Excel, but an exact lookup compares the stored values.

Common mistakes

Mistake 1: forgetting FALSE in VLOOKUP

The default is approximate match. On unsorted data it returns wrong answers without an error, so always type FALSE for exact lookups.

Mistake 2: hard-coding the column number

A VLOOKUP column number does not follow the column you meant when someone inserts a column. Here a column was inserted in front of the names, after which the three formulas were left as they were:

Column B was inserted after the formulas were written, so the data shifted right

What is inside the cells now

The VLOOKUP still says column 3, which is now the Name column: it silently returns a different piece of data. INDEX with MATCH and XLOOKUP follow the ranges, so they stay correct.

Mistake 3: not locking the table

When you copy a lookup down, the table range slides down with it and the last rows fall out of range. Lock it with dollar signs: VLOOKUP(A2, $G$2:$H$20, 2, FALSE).

Mistake 4: duplicate keys

Lookups return the first match. If a key can appear twice, decide whether you want the first, the last, or all of them (FILTER).

Mistake 5: looking up on a range that changes size

Lookup ranges that do not include new rows miss them. Use an Excel Table or whole-column references for data that grows.

Cheat sheet

Try it yourself

Use the employee table from the beginning of this guide.

1. Return the salary of employee 104.

Show solution

=VLOOKUP(104,A2:D6,4,FALSE) gives 75000. The salary is column 4 of A2:D6, and FALSE asks for an exact match.

2. Find the ID of the employee called Ravi. VLOOKUP cannot do it. Which formulas can?

Show solution

=INDEX(A2:A6,MATCH("Ravi",B2:B6,0)) gives 102. =XLOOKUP("Ravi", B2:B6, A2:A6) gives the same in Microsoft 365.

3. A mark of 68 must give a grade using a band table with the lower limits 0, 40, 60 and 75 and the grades Fail, C, B and A. Write the formula.

Show solution

=VLOOKUP(68, A1:B4, 2, TRUE) gives B. The band table must be sorted ascending.

4. Return the name of the highest-paid employee.

Show solution

=INDEX(B2:B6,MATCH(MAX(D2:D6),D2:D6,0)) gives John. MAX finds the top salary, MATCH finds its position, INDEX returns the name at that position.

Frequently asked questions

What is the difference between VLOOKUP and XLOOKUP?

VLOOKUP looks only in the first column of a table and returns a column by its number, and its default is approximate match. XLOOKUP uses separate lookup and return ranges, can look in any direction, defaults to exact match and has a built-in not-found argument. XLOOKUP needs Microsoft 365 or Excel 2021 or later.

Why does VLOOKUP return #N/A when the value exists?

The usual causes are a text-versus-number mismatch, a hidden space, a decimal calculation result, or a table range that has slid down because it was not locked with dollar signs.

What does the last argument of VLOOKUP do?

FALSE means exact match. TRUE or omitting it means approximate match, which needs the first column sorted ascending and returns the largest value that does not exceed the key.

Is INDEX MATCH better than VLOOKUP?

It is more flexible: it can look to the left and it does not depend on a column number, so inserting a column does not break it. VLOOKUP is shorter for simple cases.

How do I do a VLOOKUP with multiple criteria?

Join the criteria into one key: with XLOOKUP use a & b against col1 & col2. In older Excel, use a helper column or INDEX and MATCH with multiplied conditions.

How do I return the last match instead of the first?

In Microsoft 365 use XLOOKUP(key, keys, results, , 0, -1). In older versions use LOOKUP(2, 1/(keys = key), results).

Which Excel versions have XLOOKUP?

Microsoft 365 and Excel 2021 or later. Excel 2019 and earlier do not have it; use INDEX and MATCH there.

How do I look up a value to the left of the key?

Use INDEX with MATCH, or XLOOKUP. VLOOKUP cannot return a column to the left of the key column.