XLOOKUP vs VLOOKUP: Which Excel Function Should You Use?

August 25, 2026

Professional using Microsoft Excel during a hands-on software training session

For most people using a current version of Microsoft Excel, XLOOKUP is the better choice for new lookup formulas. It is more flexible than VLOOKUP, returns exact matches by default, can look to the left or right, and does not rely on a fixed column number that can become incorrect when a table changes.

VLOOKUP is still important, however. It remains widely used in existing spreadsheets and is supported by older versions of Excel that do not support XLOOKUP. Understanding both functions is therefore useful, particularly if you work with files created by other people.

XLOOKUP vs VLOOKUP at a glance

XLOOKUP: the preferred choice for most new lookup formulas in current Excel versions.
VLOOKUP: useful for compatibility with older workbooks and older Excel versions.
Exact match: XLOOKUP uses exact match by default; VLOOKUP requires FALSE or 0 for an exact match.
Lookup direction: XLOOKUP can return values from either side of the lookup column; VLOOKUP normally returns values only from columns to the right.
Column changes: XLOOKUP uses separate lookup and return ranges, making it more resilient when columns are inserted or removed.
Not found results: XLOOKUP includes a built-in optional argument for a friendly not-found message.
Older Excel: VLOOKUP remains useful when files must work in versions that do not support XLOOKUP.

What does VLOOKUP do?

VLOOKUP searches down the first column of a table and returns a value from another column in the same row.

Its basic syntax is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

For example:

=VLOOKUP(A2,$F$2:$H$100,3,FALSE)

This formula looks for the value in A2 in the first column of F2:H100 and returns the value from the third column of that range.

What does XLOOKUP do?

XLOOKUP searches one range or array and returns the corresponding value from another range or array.

Its basic syntax is:

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

For example:

=XLOOKUP(A2,F2:F100,H2:H100,"Not found")

This searches F2:F100 for the value in A2 and returns the corresponding result from H2:H100. If no match is found, it returns the text “Not found”.

Why is XLOOKUP easier to use?

The main reason is that XLOOKUP asks you directly for the range to search and the range to return.

With VLOOKUP, you need to select a complete table and then count which column contains the return value. If the value you need is in the seventh column, for example, you must enter 7 as the column index number.

With XLOOKUP, you simply select the lookup column and the return column. This makes the formula easier to read and reduces the risk of returning data from the wrong column.

Exact matches: XLOOKUP is safer by default

One of the most important differences is the default matching behaviour.

XLOOKUP uses an exact match by default. If it cannot find the lookup value, it returns #N/A unless you supply a custom not-found result.

VLOOKUP behaves differently. If the final range_lookup argument is omitted, VLOOKUP defaults to an approximate match. This can produce unexpected results if users forget to specify FALSE when they actually need an exact match.

For everyday tasks such as finding an employee, product, invoice or customer ID, an exact match is usually what is required.

Can XLOOKUP look to the left?

Yes. This is one of XLOOKUP's most useful advantages.

Imagine employee IDs are in column D and employee names are in column B. You want to search by employee ID and return the name.

XLOOKUP can do this directly because the return array can be on either side of the lookup array.

VLOOKUP normally requires the lookup value to be in the first column of the table array and returns data from columns to its right. Older Excel users often work around this with INDEX and MATCH or by restructuring the data.

What happens if columns are inserted?

XLOOKUP is usually more robust.

Suppose a VLOOKUP formula returns column 4 from a table. If someone inserts a new column inside that table, the required result may move to column 5. Depending on how the formula and table are structured, the formula can then return the wrong information or require editing.

XLOOKUP does not use a column index number. It refers directly to a return range, so the intention of the formula is clearer and less dependent on counting columns.

Handling missing values

With VLOOKUP, users commonly wrap the formula in IFERROR to replace #N/A with a more user-friendly message:

=IFERROR(VLOOKUP(A2,F2:H100,3,FALSE),"Not found")

XLOOKUP has this capability built in:

=XLOOKUP(A2,F2:F100,H2:H100,"Not found")

This can make formulas shorter and easier for colleagues to understand.

Approximate matches

Both functions can perform approximate matches, but they handle them differently.

VLOOKUP uses the range_lookup argument. TRUE or an omitted argument requests an approximate match, while FALSE requests an exact match.

XLOOKUP has an optional match_mode argument, which makes the requested behaviour more explicit. It can perform exact matches, return the next smaller or next larger item, and support wildcard matching.

Approximate matching can be useful for situations such as commission bands, tax thresholds, grading schemes or pricing tiers, but it should be used deliberately because the assumptions about sorted data matter.

Can XLOOKUP search from the bottom upwards?

Yes. XLOOKUP includes an optional search_mode argument that can search from the first item to the last or in reverse from the last item to the first.

This is useful when a list contains multiple occurrences of the same value and you need the most recent or final occurrence.

For example, if a customer appears several times in a transaction list, a reverse XLOOKUP can return information from the last matching row without requiring more complicated formula techniques.

Can XLOOKUP return more than one column?

In current versions of Excel that support dynamic arrays, XLOOKUP can return multiple adjacent values by using a multi-column return array.

For example, a single formula could look up an employee ID and return the employee's name, department and location into neighbouring cells.

This can reduce the need to create several separate VLOOKUP formulas for the same record.

Does XLOOKUP replace VLOOKUP?

For many everyday lookup tasks, yes. Microsoft itself describes XLOOKUP as an improved version of VLOOKUP and recommends it for current Excel users.

That does not mean VLOOKUP has become irrelevant. Millions of existing spreadsheets still contain VLOOKUP formulas, and people who support older workbooks need to understand them.

There are also organisations where workbook compatibility with older versions of Excel remains important.

Which Excel versions support XLOOKUP?

XLOOKUP is available in current Microsoft 365 and newer perpetual versions of Excel that support the function.

Microsoft specifically notes that XLOOKUP is not available in Excel 2016 or Excel 2019. A workbook created in a newer version can therefore cause compatibility problems if it is opened by someone using an older version that does not support the function.

If a workbook must be shared with people using older Excel installations, VLOOKUP, INDEX/MATCH or other compatible formulas may still be necessary.

When should you use VLOOKUP?

VLOOKUP remains a reasonable choice when:

the workbook needs to work in an older version of Excel
you are maintaining an existing spreadsheet that already uses VLOOKUP extensively
the lookup is simple and the table structure is stable
your organisation has established templates that still rely on VLOOKUP
you need to understand or troubleshoot formulas written by other users

There is no need to replace every working VLOOKUP formula purely because XLOOKUP exists. The priority should be reliability, compatibility and maintainability.

When should you use XLOOKUP?

XLOOKUP is usually the better choice when:

you are creating a new workbook in a current version of Excel
you want exact matching by default
the return value is to the left of the lookup column
you want a built-in not-found message
you need to search from the bottom upwards
you want formulas that are easier to read and maintain
the workbook structure may change over time

XLOOKUP example: product price

Suppose:

column A contains a product code entered by the user
column F contains the master product codes
column G contains product descriptions
column H contains prices

A VLOOKUP formula might be:

=VLOOKUP(A2,F:H,3,FALSE)

The equivalent XLOOKUP could be:

=XLOOKUP(A2,F:F,H:H,"Product not found")

The XLOOKUP formula makes it immediately clear that Excel searches column F and returns a value from column H.

XLOOKUP example: returning a value from the left

Suppose employee names are in column B and employee IDs are in column D.

To look up an ID in A2 and return the employee name:

=XLOOKUP(A2,D:D,B:B,"Employee not found")

That type of left lookup is straightforward with XLOOKUP but not with a standard VLOOKUP structure.

XLOOKUP vs INDEX and MATCH

Before XLOOKUP was introduced, experienced Excel users often preferred INDEX and MATCH to VLOOKUP because the combination could perform more flexible lookups and was less dependent on the position of the return column.

INDEX and MATCH remain valuable skills, especially when maintaining older workbooks or building more specialised formulas. However, XLOOKUP now handles many common situations more simply.

ExperTrain's Excel Advanced Functions & Data Modelling course covers XLOOKUP alongside INDEX, MATCH and other modern Excel functions.

Should you convert old VLOOKUP formulas to XLOOKUP?

Not automatically.

If an existing workbook works reliably and needs to remain compatible with older Excel versions, leaving VLOOKUP in place may be sensible.

Conversion becomes more attractive when:

the workbook is being redesigned
the current formulas are difficult to maintain
column insertion regularly causes problems
the organisation has moved fully to Microsoft 365 or another XLOOKUP-compatible version
you want clearer error handling and lookup logic

Why do I still need to learn VLOOKUP?

Even if XLOOKUP is your preferred function, VLOOKUP remains one of the most common formulas found in business spreadsheets.

You may need to:

audit a workbook created years ago
repair a colleague's formula
understand a report inherited from another team
support a customer or supplier using an older Excel version
convert an older process to a newer formula approach

For this reason, learning VLOOKUP is still worthwhile. The goal is not to memorise one “best” function but to understand which lookup technique suits the workbook you are working with.

Which Excel course covers XLOOKUP?

If you are still developing your lookup-function skills, ExperTrain's Microsoft Excel Advanced course covers VLOOKUP and other advanced formula techniques.

For more experienced users, the Microsoft Excel Advanced Functions & Data Modelling course covers XLOOKUP, INDEX, MATCH, dynamic array functions and more advanced reporting and data-modelling techniques.

If you are unsure which level is most appropriate, the free Excel Skills Assessment can recommend a suitable starting point based on your current knowledge.

Frequently asked questions

Is XLOOKUP better than VLOOKUP?

For most new formulas in a current version of Excel, yes. XLOOKUP is more flexible, uses exact match by default, supports lookups in either direction and is easier to maintain.

Is VLOOKUP obsolete?

No. It still works and remains widely used. It is also important when workbook compatibility with older versions of Excel is required.

Why does Microsoft recommend XLOOKUP?

Microsoft describes XLOOKUP as an improved version of VLOOKUP because it can work in any direction and returns exact matches by default.

Can XLOOKUP replace INDEX and MATCH?

It can replace INDEX/MATCH in many common lookup situations, although INDEX and MATCH remain useful and can still be appropriate for specialised or older workbooks.

Can XLOOKUP return a value to the left?

Yes. The lookup array and return array are specified separately, so the return range can be either to the left or right of the lookup range.

Can VLOOKUP look left?

Not in its standard form. The lookup value needs to be in the first column of the table array and the return column normally needs to be to its right.

Does XLOOKUP need FALSE for an exact match?

No. Exact match is XLOOKUP's default behaviour.

Does VLOOKUP need FALSE for an exact match?

Yes. If you want a normal exact match, explicitly use FALSE or 0 as the final argument. Omitting it requests approximate-match behaviour.

Why does my XLOOKUP show #NAME?

A common reason is that the workbook is being opened in a version of Excel that does not support XLOOKUP. Check the Excel version being used.

Should beginners learn VLOOKUP or XLOOKUP first?

If everyone using the workbook has a current version of Excel, XLOOKUP is a sensible function to learn first. It is still valuable to understand VLOOKUP because of its widespread use in existing spreadsheets.

Develop your Excel skills with ExperTrain

ExperTrain provides live instructor-led Excel training from beginner through advanced and specialist levels.

You may also find the Excel Glossary useful for quick explanations of Excel functions, features and terminology.