VLOOKUP vs XLOOKUP vs INDEX MATCH: which lookup to use

Sooner or later every spreadsheet needs a lookup. You have a list of order lines with a product code, and the prices are on another sheet. You have employee IDs and need their department. You have a sales figure and need the commission band it falls in. Typing those values by hand works for ten rows and fails at a thousand.

Excel gives you three main ways to do it: VLOOKUP, XLOOKUP, and INDEX with MATCH. They overlap a lot, which is why the question "which one should I use?" comes up so often. This guide explains how each one works, where each one breaks, and gives you a clear answer for every situation you are likely to meet.

What a lookup does

Every lookup does the same three things:

1. Takes a value to look for, such as a product code or an employee ID.
2. Finds it in a column or row to search.
3. Returns the value from the same position in a column or row to return.

The differences between the functions are in how you tell Excel where those columns are, what happens when the value is not found, and what happens when the value is not an exact match. Keep those three questions in mind and the rest falls into place.

VLOOKUP: the one everyone knows

VLOOKUP has been in Excel since the beginning, and it is in almost every workbook older than a few years. Its syntax is:

=VLOOKUP(E1,A2:C5,3,FALSE)

Read it as: look up E1 in the first column of A2:C5, and return the value from the third column of that table, with an exact match.

The four arguments are:

• lookup_value: what to find, here E1.
• table_array: the whole table, and VLOOKUP always searches its first column.
• col_index_num: which column of the table to return, counted from the left edge of the table, starting at 1.
• range_lookup: FALSE for an exact match, TRUE (or nothing) for an approximate match.

Practice the basic form in looking up a price with VLOOKUP and with a number as the key.

Where VLOOKUP breaks

VLOOKUP's design causes four problems you should know about, because they are behind most lookup errors in real workbooks.

It defaults to an approximate match. If you leave out the fourth argument, VLOOKUP assumes TRUE. On an unsorted list, that returns a wrong value without any error. This is the most common VLOOKUP mistake by far, and the reason to make FALSE a habit.

It can only look to the right. The value you search for must be in the first column of the table. If the ID is in column C and the name you want is in column A, VLOOKUP cannot do it without rearranging the data.

The column number is a fixed number. 3 means the third column of the table. Insert a column inside the table and the formula still says 3, now pointing at the wrong column. Nothing warns you.

It returns #N/A when the value is missing. That is honest, but ugly in a report. You need IFERROR or IFNA around it to show something friendlier.

When the approximate match is what you want

TRUE is not always a mistake. For bands, such as discount levels, tax brackets or grade boundaries, an approximate match is exactly right, as long as the first column is sorted from smallest to largest.

VLOOKUP with TRUE finds the largest value in the first column that is less than or equal to the lookup value. 6,200 is not in the list, so it settles on 5,000 and returns a 10% discount. The approximate match exercise drills this. If the list is not sorted, the result is unpredictable, so sort first.

XLOOKUP: the modern replacement

XLOOKUP arrived in Excel for Microsoft 365 and Excel 2021, and Google Sheets supports it too. It was designed to fix VLOOKUP's problems, and for most new work it is the one to use.

=XLOOKUP(E2,A2:A6,C2:C6)

Read it as: look up E2 in A2:A6, and return the value from the same row of C2:C6.

Instead of a table and a column number, you give two ranges: where to search and what to return. That small change solves three of VLOOKUP's four problems at once:

• It defaults to an exact match. No FALSE to forget.
• It can look in any direction. The return range can be left or right of the search range, or on another sheet.
• It survives inserted columns. The return range is a range, not a number, so it moves with the data.

And the fourth problem has an argument of its own:

The fourth argument, if_not_found, is what XLOOKUP returns when there is no match. That replaces the IFERROR wrapper you need with VLOOKUP. Practice both forms in looking up a price with XLOOKUP and XLOOKUP with a not-found value.

XLOOKUP has two more optional arguments. match_mode chooses exact match (0, the default), next smaller (-1), next larger (1) or wildcard (2). search_mode chooses whether to search from the first item (1, the default) or the last (-1), which is useful for finding the most recent entry for a customer. You will rarely need them, but match_mode -1 is how you do band lookups without VLOOKUP's TRUE.

Where XLOOKUP falls short

XLOOKUP has one real limitation: availability. Excel 2019 and earlier do not have it. If you send a workbook with XLOOKUP to someone on an older version, they see #NAME? in every cell. Many companies still run older versions on some machines, and some online tests use a simulator without it. That is the main reason to keep VLOOKUP and INDEX MATCH in your toolkit.

The other thing to watch is that the search range and the return range must be the same size. =XLOOKUP(E2,A2:A6,C2:C7) returns #VALUE!. That is a feature, really: VLOOKUP would happily return a value from the wrong row.

INDEX and MATCH: the flexible pair

Before XLOOKUP, the way to get around VLOOKUP's limits was to combine two functions, INDEX and MATCH. It is still the most flexible lookup, and it works in every version of Excel and in every other spreadsheet program.

The two halves do one job each:

• MATCH finds the position of a value in a range: =MATCH(D2,A2:A7,0) returns 3 if D2 is the third item. The 0 means exact match.
• INDEX returns the value at a position in a range: =INDEX(C2:C7,3) returns the third value of C2:C7.

Put MATCH inside INDEX and you have a lookup: =INDEX(C2:C7,MATCH(E2,A2:A7,0)). MATCH finds the row, INDEX returns the value from that row. Learn MATCH on its own first, then the combination.

Because the search range and the return range are separate, INDEX MATCH can look left:

That is something VLOOKUP simply cannot do. The look to the left exercise asks for exactly this.

Two-way lookups

INDEX can take a row number and a column number. Give it two MATCHes and you can look up a value at the crossing of a row and a column, such as one sales rep's figure for one month:

This is one of the formulas that separates intermediate from advanced users in tests. It reads in plain words as: in the block B2:D4, go to the row where Ben is and the column where Mar is. You can do the same with two nested XLOOKUPs, but INDEX with two MATCHes is the classic answer and works everywhere.

The price of flexibility

INDEX MATCH has two downsides. It is longer to type and harder to read for someone who has not seen it before, and MATCH needs its third argument, 0, for an exact match. Leave it out and MATCH defaults to an approximate match, with the same silent wrong answers as VLOOKUP's TRUE. Make the 0 a habit, as you do with VLOOKUP's FALSE.

Excel 365 also has XMATCH, a modern MATCH that defaults to an exact match and can search from the end. =INDEX(C2:C7,XMATCH(E2,A2:A7)) is a safer INDEX MATCH if everyone using the file has a recent Excel.

Side by side

Here is how the three compare on the points that matter in practice:

HLOOKUP, VLOOKUP's horizontal twin, searches the first row of a table instead of the first column. You will see it in old workbooks with months across the top. XLOOKUP and INDEX MATCH handle rows and columns the same way, so you do not need HLOOKUP for new work.

Which one to use

With all that said, the decision is quite simple:

• Building something new, everyone has Microsoft 365 or Excel 2021+: use XLOOKUP. It is the easiest to read and the hardest to get wrong.
• The file will be opened in older versions of Excel: use INDEX MATCH, or VLOOKUP if the key is in the first column and the table will not change.
• Editing an existing workbook full of VLOOKUPs: stay consistent and use VLOOKUP, unless you are rebuilding the sheet anyway. A mix of three lookup styles in one file is harder to maintain than any one of them.
• Looking up bands or brackets: VLOOKUP with TRUE on a sorted list, or XLOOKUP with match_mode -1.
• Looking up at the crossing of a row and a column: INDEX with two MATCHes.
• Taking a test: use whatever the task asks for. If it does not say, use the one you are fastest and surest with, and know VLOOKUP well enough to write it without thinking, because it is the one most tests expect.

When a lookup returns #N/A

Every lookup returns #N/A when it cannot find the value. Sometimes that is correct: the product code really is not in the price list. More often, the value is there but does not match exactly. Check these, in this order:

1. Extra spaces. "A-104 " with a trailing space does not match "A-104". Look at the cell with F2; the cursor shows the space. Fix it with TRIM in a helper column, or =VLOOKUP(TRIM(E1),A2:C5,3,FALSE).
2. Numbers stored as text. An ID imported from another system may be the text "103" rather than the number 103. They look the same but do not match. Text aligns left by default, numbers right, which is a quick clue.
3. The wrong range. The table_array or search range does not include the row with the value, often because the formula was filled down without $ and the range slid down with it. Lock the range: $A$2:$C$5.
4. An approximate match where you wanted exact. VLOOKUP without FALSE or MATCH without 0 can return #N/A on a value that is in the list, if the list is not sorted.

Once you know the value is genuinely missing, decide what the sheet should show. In a report, wrap the lookup in IFERROR: =IFERROR(VLOOKUP(D2,A2:B6,2,FALSE),"Unknown"), as in the IFERROR exercise. IFNA does the same but only catches #N/A, so a broken formula still shows its error, which is safer when you are checking your work. With XLOOKUP, use the fourth argument.

Lookup habits that save hours

A few habits make lookups more reliable, whichever function you use:

• Lock your ranges with $ before you fill a lookup down. Press F4 right after selecting the range.
• Look up by an ID, not a name. Names have spelling variants, middle names and duplicates. IDs, codes and email addresses are better keys.
• Keep lookup tables in one place, on their own sheet, formatted as an Excel table. Formulas that point at a table name grow with the data.
• Check for duplicates in the key column. Every lookup returns the first match. If a product code appears twice with different prices, you get the first one and no warning. A quick =COUNTIF(A2:A100,A2) in a helper column shows any key that appears more than once.
• Test with a value you know. Before you trust a lookup on 5,000 rows, check three of them by hand.

Moving a workbook from VLOOKUP to XLOOKUP

If your team has moved to Microsoft 365, you may want to replace old VLOOKUPs with XLOOKUP. It is worth doing when you are already rebuilding a sheet, and rarely worth doing for its own sake. If you do it, the translation is mechanical:

• The lookup value stays the same.
• The search range is the first column of the old table_array.
• The return range is the column the old col_index_num pointed at.
• Drop the FALSE, because XLOOKUP matches exactly by default. If the old formula used TRUE for bands, add -1 as the fifth argument (match_mode, next smaller item).
• If the old formula had IFERROR around it, move the fallback value into XLOOKUP's fourth argument.

So =VLOOKUP(F2,A2:C5,3,FALSE) becomes =XLOOKUP(F2,A2:A5,C2:C5). Change one formula, check it against the old result on a few rows, then fill it down. Keep a copy of the original file until the new version has been used for a full reporting cycle.

Lookups in Excel tests

Lookups are the most common intermediate question in employer Excel tests, and the most common reason good candidates lose points. The tasks usually take one of these forms:

• A simple exact lookup: "Return the department of the employee in E1." Any function works, but the test may name one.
• A lookup with a fallback: "Show Not found if the ID is not in the list." That is XLOOKUP's fourth argument, or IFERROR around VLOOKUP.
• A band lookup: "Find the commission rate for each sales figure using the table in G1:H5." Approximate match on a sorted table.
• A left lookup: the key is to the right of the value you need. That rules out VLOOKUP and is there to see if you know INDEX MATCH or XLOOKUP.
• A lookup filled down a column: every row needs its own lookup, and the test checks every row. Forget the $ on the table and the lower rows break.

Under time pressure, the two checks that catch most errors are: did I add FALSE (or 0 in MATCH), and did I lock the table with $? If a test also asks you to explain your choice, a short answer such as "XLOOKUP, because the key is to the right of the value and it handles not found" is exactly what the marker wants to see. Our job interview guide has more on how these tests work.

Summary

All three lookups find a value and return something from the same row. VLOOKUP is the one you will meet most, needs FALSE for an exact match and only looks right. XLOOKUP is simpler, safer and more capable, but only in recent versions of Excel. INDEX with MATCH works everywhere, looks in any direction and does two-way lookups, at the cost of being harder to read.

Learn VLOOKUP properly first, because it is everywhere. Then use XLOOKUP for your own work, and keep INDEX MATCH for older files and two-way lookups. The exercises below take you through all three, in that order.