How to Choose Between XLOOKUP, VLOOKUP, and INDEX MATCH in Excel

Lookup formulas are easier to maintain when the formula matches the workbook rather than following a rule that one function is always better. VLOOKUP remains useful in older workbooks, XLOOKUP is usually the clearest choice in current versions of Excel, and INDEX with MATCH is still valuable when compatibility or flexible references matter.

None of these functions prevents Excel crashes by itself. Workbook stability depends on factors such as file size, volatile formulas, external connections, formatting, add-ins, hardware, and the number of calculations Excel must perform. The practical goal is to choose a lookup that is correct, readable, and compatible with the people who will open the file.

Start with the Workbook's Compatibility Requirements

Before changing a formula, confirm which Excel versions are used by colleagues, clients, or automated systems. XLOOKUP is available in Microsoft 365 and newer perpetual versions, but it is not available in Excel 2016 or Excel 2019. A workbook that depends on XLOOKUP may therefore display an unsupported-function error on an older installation.

Use Exact Matching for IDs and Codes

Consider a worksheet where column A contains a product ID, column B contains a name, column C contains a price, and column D contains stock. Cell F2 contains the ID to find.

An XLOOKUP formula that returns the price is:

=XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100,"Not found")

XLOOKUP uses exact matching by default. The lookup array and return array are separate, which makes the intended result visible in the formula.

The equivalent VLOOKUP formula is:

=VLOOKUP(F2,$A$2:$D$100,3,FALSE)

The final FALSE requests an exact match. Omitting that argument can lead to an approximate match, which is usually inappropriate for product IDs, invoice numbers, employee codes, and similar identifiers.

The INDEX and MATCH version is:

=INDEX($C$2:$C$100,MATCH(F2,$A$2:$A$100,0))

The 0 in MATCH requests an exact match. MATCH finds the relative row, and INDEX returns the value from the corresponding row in column C.

Choose XLOOKUP When Readability Is the Priority

XLOOKUP is often the most readable option for a workbook that will stay in supported versions of Excel. It can return a value from a column to the left or right of the lookup column, and it includes a dedicated result for missing matches.

For example, this formula looks up a product name in column B and returns the ID from column A:

=XLOOKUP(F2,$B$2:$B$100,$A$2:$A$100,"Not found")

VLOOKUP cannot perform that leftward lookup without rearranging the source range or using another approach.

Keep VLOOKUP When the Existing Formula Is Clear and Stable

A correct VLOOKUP does not need to be replaced merely because another function exists. Keep it when the workbook must support older Excel versions, the lookup column is already first, the return column is unlikely to move, and the formula is understood by the people maintaining the workbook.

Review the column index whenever columns are inserted or deleted inside the table array. In VLOOKUP(F2,A:D,3,FALSE), the number 3 means the third column of that selected range. A structural change can cause the formula to return a different field even though the formula itself does not show an error.

Use INDEX and MATCH for Flexible Legacy Workbooks

INDEX and MATCH is useful when XLOOKUP is unavailable but the lookup must return data from either side of the key column. It also separates the lookup range from the return range, so moving an unrelated column does not change a numeric column index.

For two-way lookups, a second MATCH can identify the return column. Suppose row 1 contains month names and column A contains product IDs:

=INDEX($B$2:$M$100,
       MATCH($P2,$A$2:$A$100,0),
       MATCH(Q$1,$B$1:$M$1,0))

The first MATCH finds the product row. The second MATCH finds the month column. This is useful in structured reports, but the row and column labels still need consistent spelling and data types.

Convert Source Data to an Excel Table

Absolute ranges such as $A$2:$A$100 are easy to understand in a small example, but they require maintenance when rows are added. Converting the source range to an Excel Table allows formulas to use column names and expand with new rows.

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

Structured references also make it easier to inspect whether the lookup and return fields are correct. Give tables and columns clear names rather than leaving default labels such as Table1 or Column3.

Check the Data Before Blaming the Formula

Many lookup failures are caused by inconsistent source data rather than the function itself. Check these conditions before rewriting the formula:

• A number stored as text will not always match a numeric value.
• Leading or trailing spaces can create visually identical but different text.
• Duplicate keys can return the first matching record rather than the intended one.
• Blank cells may be different from cells containing an empty string returned by another formula.
• Dates must be stored as valid Excel dates, not only displayed in a date-like format.

Do not wrap every lookup in IFERROR before understanding the error. A friendly “Not found” result is helpful for expected missing records, but broad error handling can also hide a broken range, misspelled table name, or incompatible function.

Test a Lookup Before Filling It Down

1. Test a key that appears once and should return a known value.
2. Test a missing key and confirm the intended message appears.
3. Test a duplicate key and decide which record should win.
4. Add a new source row and confirm the lookup range expands.
5. Insert a column and confirm the result remains correct.
6. Open the workbook in the oldest Excel version that must support it.

Official Microsoft References

Microsoft provides current documentation for the XLOOKUP function, the VLOOKUP function, and lookup examples using VLOOKUP, INDEX, and MATCH.

A Practical Selection Rule

Use XLOOKUP for new workbooks when every required Excel version supports it. Keep a correct VLOOKUP when compatibility and simplicity matter. Use INDEX and MATCH when an older workbook needs flexible lookup and return ranges. The best formula is the one that returns the correct result, survives expected worksheet changes, and can be understood by the next person who maintains the file.