XLOOKUP is the best choice for most users on Microsoft 365 or Excel 2021+: it looks in any direction, defaults to an exact match, and handles errors natively. INDEX/MATCH is the flexible fallback for older Excel versions and left-of-key lookups, while VLOOKUP still works well for simple, left-to-right lookups in legacy files shared with older Excel.
If you build reports, reconcile lists, or pull a price or name from a table, you rely on a lookup function every day. Excel gives you three main options, and the “right” one depends on your Excel version and which direction you need to look. Here’s how they compare, with the exact syntax for each and a simple rule for choosing.
What’s the difference between VLOOKUP, XLOOKUP, and INDEX/MATCH?
All three answer the same question: “find a value in a table and return something related to it.” They differ in flexibility, direction, and how easily they break.
- VLOOKUP searches the leftmost column of a range and returns a value from a column to its right, identified by a number. It’s the quickest to type and the easiest to break.
- INDEX/MATCH combines two functions: MATCH finds the position of your lookup value, and INDEX returns the value at that position. It can look in any direction and rarely breaks when columns move.
- XLOOKUP is the modern single-function replacement. It looks left or right, up or down, defaults to exact match, and has built-in error handling. It’s only available in newer Excel.
How does VLOOKUP work (and where does it fall short)?
VLOOKUP syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Example — find an employee’s department from a staff table in A2:D50, where names are in column A and department is the 3rd column:
=VLOOKUP("J. Smith", A2:D50, 3, FALSE)The FALSE forces an exact match; always include it, because the default is an approximate match that can silently return the wrong row.
VLOOKUP’s three weaknesses:
- It can only look right. The lookup column must sit to the left of the value you want. If your ID sits to the right of the name you’re searching, VLOOKUP can’t help.
- It breaks when columns change. The 3 is a fixed column number. Insert a new column into the table and VLOOKUP still points at position 3, now returning the wrong field.
- No native error handling. A missing value returns #N/A unless you wrap the whole thing in IFERROR.
How does INDEX/MATCH work?
INDEX/MATCH solves VLOOKUP’s direction and fragility problems. MATCH returns the position of a value in a row or column; INDEX returns whatever sits at that position in another range.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))Same employee example, returning the department in column C by matching the name in column A:
=INDEX(C2:C50, MATCH("J. Smith", A2:A50, 0))The 0 in MATCH means exact match. Because you reference the return column and the lookup column separately, INDEX/MATCH can look left, and inserting columns won’t break it. The trade-off is that it’s two functions nested together, which takes longer to write and read than a single lookup.
How does XLOOKUP work?
XLOOKUP folds everything above into one function:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Same example, with a friendly message if the name isn’t found:
=XLOOKUP("J. Smith", A2:A50, C2:C50, "Not found")What makes XLOOKUP the default choice on modern Excel:
- Any direction. You name the lookup array and the return array separately, so it looks left, right, up (with vertical arrays), or down.
- Exact match by default. No FALSE or 0 to remember, and no accidental approximate matches.
- Built-in “if not found”. The fourth argument replaces the need to wrap it in IFERROR.
- Robust to column changes. Like INDEX/MATCH, it references ranges directly, so inserting columns doesn’t break it.
- Returns whole arrays. Point the return array at several columns and XLOOKUP spills all of them at once.
The catch is version support, covered below.
Which lookup function is faster?
For everyday spreadsheets the speed difference is negligible; you will not notice it on a few hundred or few thousand rows. The difference only shows up in very large models with tens of thousands of formulas.
In those large workbooks, INDEX/MATCH and XLOOKUP tend to be lighter than VLOOKUP when VLOOKUP references an entire wide table, because they only scan the specific lookup and return arrays rather than the full table block. XLOOKUP references two arrays and is broadly comparable to INDEX/MATCH in practice. The bigger performance lever is almost always good spreadsheet design, referencing tight ranges instead of whole columns, not which of the three functions you pick.
Does XLOOKUP work in older versions of Excel?
No. XLOOKUP is available in Microsoft 365 and Excel 2021 (and later), on Windows, Mac, and the web. It is not available in Excel 2019, 2016, or earlier. If you build a workbook with XLOOKUP and send it to someone running Excel 2019, they will see a #NAME? error where the formula should be.
This is the single biggest reason INDEX/MATCH and VLOOKUP are still worth knowing: both work in every modern version of Excel, so they’re the safe choice for files shared across an organisation that hasn’t fully upgraded.
Which one should you use?
Use this rule:
- On Microsoft 365 or Excel 2021+ and the file stays in-house? Use XLOOKUP. It’s the simplest to write correctly and the hardest to break.
- Need to look to the left, or share the file with people on Excel 2019 or older? Use INDEX/MATCH. It’s flexible and universally compatible.
- Simple right-of-key lookup in a legacy file, or a quick one-off? VLOOKUP is fine, just remember the FALSE.
| Feature | VLOOKUP | INDEX/MATCH | XLOOKUP |
|---|---|---|---|
| Looks left | No | Yes | Yes |
| Survives inserted columns | No | Yes | Yes |
| Exact match by default | No (needs FALSE) | No (needs 0) | Yes |
| Built-in “if not found” | No | No | Yes |
| Ease of writing | Easiest | Hardest | Easy |
| Works in Excel 2019 & earlier | Yes | Yes | No |
| Best for | Quick legacy lookups | Compatibility + flexibility | Modern default |
Still juggling all three in real workbooks? Our hands-on Advanced Excel Training Course walks you through lookup functions on real business data with a Microsoft Certified Trainer, and the Excel for Data Analysis Course takes you further into Power Query and Power Pivot. To build your Excel skills end to end, browse our full range of Microsoft Excel training courses, and if you’re new to the newest function, start with XLOOKUP Explained.
Frequently asked questions
VLOOKUP searches only the leftmost column and returns a value to its right using a fixed column number, which breaks if columns move. XLOOKUP looks in any direction, defaults to exact match, and has built-in error handling, but it only works in Microsoft 365 and Excel 2021 or later.
For flexibility, yes. INDEX/MATCH can look to the left and doesn’t break when you insert columns, and it works in every modern Excel version. The trade-off is that it combines two functions, so it takes a little longer to write and read than VLOOKUP.
On normal-sized spreadsheets the difference is negligible. In very large models, INDEX/MATCH and XLOOKUP are usually lighter than a VLOOKUP that references an entire table, because they only scan the specific lookup and return ranges. Good range design matters more than the function you choose.
Yes. Unlike VLOOKUP, XLOOKUP lets you name the lookup array and the return array separately, so it can return a value from a column to the left of your search column.
No. XLOOKUP requires Microsoft 365 or Excel 2021 or later. In Excel 2019, 2016, or earlier it returns a #NAME? error, so use INDEX/MATCH or VLOOKUP for files shared with older versions.
Yes, in the right situations. VLOOKUP is quick to write for simple left-to-right lookups and works in every Excel version, which makes it useful for legacy files. But if you’re on Microsoft 365 or Excel 2021+, XLOOKUP is the better default.
