U Love Office
Home › Blog › Excel & Data
Excel & Data

XLOOKUP vs VLOOKUP: Which Excel Formula Should You Use?

✍️ admin 📅 08 August 2026

XLOOKUP vs VLOOKUP: which to use, with copy-paste examples

If you’re deciding between XLOOKUP vs VLOOKUP, use XLOOKUP when your version of Excel supports it. XLOOKUP is more flexible because it can search in any direction, return values from columns to the left or right, and uses exact matching by default. VLOOKUP is still useful when working with older Excel versions or existing spreadsheets that already use it. For most new Excel workbooks, XLOOKUP is the better choice. This guide explains the differences with simple copy-paste examples, so you can choose the right formula without memorizing complicated syntax.

XLOOKUP vs VLOOKUP
XLOOKUP vs VLOOKUP

What is VLOOKUP?

VLOOKUP stands for Vertical Lookup.

It searches for a value in the first column of a selected table and returns a corresponding value from another column in the same table.

The basic formula is:

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

For example:

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

This searches for the value in A2 in the first column of F2:H10 and returns the corresponding value from the third column.

VLOOKUP has been widely used in Excel for years, so you will still encounter it in many existing spreadsheets.

What is XLOOKUP?

XLOOKUP is a newer Excel lookup function designed to address several limitations of VLOOKUP.

The basic formula is:

=XLOOKUP(lookup_value, lookup_array, return_array)

For example:

=XLOOKUP(A2,$F$2:$F$10,$H$2:$H$10)

This searches column F for the value in A2 and returns the corresponding value from column H.

Unlike VLOOKUP, you don’t need to specify a column number.

XLOOKUP vs VLOOKUP: the key differences

The biggest differences are:

Feature XLOOKUP VLOOKUP
Exact match default Yes No
Search left Yes No
Search right Yes Yes
Column number required No Yes
Can return multiple columns Yes Limited
Search from bottom Yes No
Custom “not found” message Yes Requires IFERROR
Approximate matching Yes Yes
Works with older Excel versions No Yes

For new spreadsheets, XLOOKUP generally provides a cleaner and more flexible approach.

How to use VLOOKUP

Let’s say you have this employee table:

Employee ID Employee Salary
E001 Ahmed AED 8,000
E002 Sara AED 9,500
E003 John AED 7,500

You enter an employee ID in cell A2 and want to retrieve the salary.

Use:

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

If A2 contains E002, Excel returns:

AED 9,500

Step-by-step

  1. Enter the employee ID.
  2. Select the table containing the lookup data.
  3. Select the column number containing the result.
  4. Use FALSE for an exact match.
  5. Press Enter.

The formula can then be copied down for additional employees.

How to use XLOOKUP

Using the same data, the XLOOKUP formula is:

=XLOOKUP(A2,$F$2:$F$4,$H$2:$H$4)

If A2 contains E002, the result is:

AED 9,500

Notice that you don’t specify 3 for the salary column.

You tell Excel exactly which column to search and exactly which column to return.

Step-by-step

  1. Enter the employee ID.
  2. Select the lookup column.
  3. Select the return column.
  4. Press Enter.
  5. Copy the formula down if required.

Worked example with AED figures

Imagine a Dubai company has the following employee salary data:

Employee ID Employee Department Monthly Salary
E101 Ahmed IT AED 12,000
E102 Fatima HR AED 10,500
E103 Omar Finance AED 14,000
E104 Sara Sales AED 11,500

You want to retrieve Omar’s salary using employee ID E103.

VLOOKUP

If the table is in F2:I5:

=VLOOKUP(A2,$F$2:$I$5,4,FALSE)

Result:

AED 14,000

XLOOKUP

Use:

=XLOOKUP(A2,$F$2:$F$5,$I$2:$I$5)

Result:

AED 14,000

The XLOOKUP version is easier to understand because the formula directly identifies the lookup and return ranges.

When should you use XLOOKUP?

Choose XLOOKUP when:

For example:

=XLOOKUP(A2,$F$2:$F$5,$I$2:$I$5,”Not found”)

If the employee ID doesn’t exist, Excel displays:

Not found

This is more convenient than building an additional error-handling formula around VLOOKUP.

When should you use VLOOKUP?

VLOOKUP still makes sense when:

You don’t need to replace every existing VLOOKUP formula just because XLOOKUP is available.

XLOOKUP can look to the left

This is one of the most important differences.

Suppose your data looks like this:

Employee Employee ID Salary
Ahmed E101 AED 12,000
Fatima E102 AED 10,500

You want to find the employee name using the employee ID.

XLOOKUP can do this:

=XLOOKUP(A2,$G$2:$G$3,$F$2:$F$3)

The lookup column is to the right of the result column.

Standard VLOOKUP cannot perform this leftward lookup directly.

How to handle missing values

With XLOOKUP:

=XLOOKUP(A2,$F$2:$F$5,$I$2:$I$5,”Employee not found”)

With VLOOKUP:

=IFERROR(VLOOKUP(A2,$F$2:$I$5,4,FALSE),”Employee not found”)

Both can provide a friendly result, but XLOOKUP has the option built directly into the function.

Common mistakes to avoid

When using either lookup function, check these common problems:

Using absolute references such as $F$2:$I$5 helps prevent lookup ranges from moving when formulas are copied.

Frequently asked questions

Is XLOOKUP better than VLOOKUP?

For most new Excel workbooks, XLOOKUP is more flexible and easier to maintain. VLOOKUP remains useful for compatibility with older Excel versions and existing spreadsheets.

Should I learn VLOOKUP or XLOOKUP first?

If you’re learning Excel today and your version supports XLOOKUP, learn XLOOKUP first. Understanding VLOOKUP is still valuable because it remains common in existing workbooks.

Can XLOOKUP replace VLOOKUP?

In many situations, yes. XLOOKUP can perform standard lookup tasks handled by VLOOKUP while also providing additional capabilities.

Why does VLOOKUP return the wrong result?

Common causes include using approximate matching unintentionally, selecting the wrong column number, or having inconsistent data types. Use FALSE when you need an exact match.

Does XLOOKUP work in all versions of Excel?

No. XLOOKUP is available in newer versions of Excel, including Microsoft 365 and supported newer perpetual versions. Older Excel versions may not support it.

Conclusion

When comparing XLOOKUP vs VLOOKUP, XLOOKUP is generally the better choice for new Excel workbooks because it is more flexible, easier to read, and handles many lookup situations with less complicated syntax. VLOOKUP remains important for older Excel versions and existing spreadsheets. The key is understanding both functions so you can work confidently with different files. For more spreadsheet and document utilities, visit uLoveOffice.com, where you can access online tools for working with Excel and PDF files as often as needed.