XLOOKUP vs. VLOOKUP in Excel: Key Differences, Examples, and Uses

Summary: Microsoft Excel includes lookup tools for finding data. VLOOKUP searches the first column and returns values to its right, while XLOOKUP retrieves values in either direction and defaults to an exact match. XLOOKUP also supports reverse searches and custom messages for missing values. However, it isn’t available in Excel 2016 and 2019, so VLOOKUP remains useful for older workbooks.

Excel becomes easier to work with when you know which formula to use for a task. Whether you are tracking student marks, organizing job applications, or managing product data, lookup functions can help you retrieve information without manually searching through rows. VLOOKUP is familiar, but XLOOKUP offers more flexibility in how you find and return values. Understanding XLOOKUP vs. VLOOKUP can help you work with spreadsheets more efficiently. This article explains both functions with examples, a comparison table, and tips for avoiding common errors.

What Is VLOOKUP in Excel?

VLOOKUP stands for Vertical Lookup. It searches for a value in the first column of a selected table and returns related information from another column in the same row. For example, you can use it to find a product’s price by name or retrieve a student’s marks by roll number.

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

The formula contains four arguments:

• lookup_value: The value you want to find.
• table_array: The range containing the search value and the information you want to retrieve.
• col_index_num: The number of the column containing the result, counted from the first one of the selected range.
• range_lookup: An optional argument that specifies whether you want an exact or an approximate match. Use FALSE for an exact match and TRUE for an approximate match.

If you omit the fourth argument, VLOOKUP defaults to approximate matching. For most everyday lookups involving employee IDs, product names, or roll numbers, use FALSE to avoid unexpected results.

Example: 

Suppose column A contains product names and column B contains their prices. To find the price of a bag, use:=VLOOKUP(“Bag”, A2:B10, 2, FALSE). Excel searches for “Bag” in column A and returns its corresponding price from column B.

Read More: Want to learn how to use VLOOKUP in Excel? Read our guide on the VLOOKUP Formula in Excel to understand how to find and retrieve data easily.

What Is XLOOKUP in Excel?

XLOOKUP is a lookup function that searches one range and returns the corresponding value from another range. Unlike VLOOKUP, it does not require the search range to be the first column of a larger table. The result can come from a column to the left or right of the search range, or from a corresponding row when working horizontally.

XLOOKUP also supports exact matching by default and lets you specify what Excel should display when it cannot find a match.

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

The six arguments include:

• lookup_value: The value you want to find.
• lookup_array: The range or array where Excel should search.
• return_array: The range containing the result you want.
• if_not_found: An optional message or value to return when no match exists.
• match_mode: Specifies the type of match. By default, it is an exact match.
• search_mode: Specifies the search order, including searching from the first or the last item.

Example: 

Suppose employee IDs are in column C and employee names are in column A. To find the name associated with employee ID 102, use:=XLOOKUP(102, C2:C10, A2:A10, “Employee not found”) Excel searches column C and returns the corresponding name from column A. If the ID is absent, the formula displays “Employee not found” instead of the default #N/A error.

Read More: Make your spreadsheet tasks faster with our guide to Excel Shortcut Keys. Learn keyboard shortcuts to boost your productivity.

XLOOKUP vs. VLOOKUP: Key Differences

Both functions differ in their formulas and capabilities. The following comparison highlights a quick comparison of Excel XLOOKUP vs. VLOOKUP: 

Feature	VLOOKUP	XLOOKUP
Lookup Orientation	Searches vertically in a table	Searches vertically or horizontally
Return Direction	Returns a value from a column to the right of the search column	Returns values from either side of the search range
Search and Result Ranges	Uses one table range	Uses separate lookup and return ranges
Result Reference	Requires a column index number	Requires a return range
Default Matching	Approximate match	Exact match
Missing Values	Returns #N/A when an exact match is not found	Can display a custom message; otherwise returns #N/A
Search Order	Searches from top to bottom	Can search from the beginning or the end
Column Changes	A column index can point to the wrong result after structural changes	Separate ranges avoid counting columns, although edits to the referenced ranges can still affect results
Compatibility	Available in Excel 2016, 2019, 2021, 2024, and Microsoft 365	Not available in Excel 2016 and 2019
Approximate Matching	Supported through TRUE	Supports next-smaller, next-larger, and wildcard matching options

i. Search Direction: Vertical and Horizontal Lookups

VLOOKUP searches vertically down the first column of the selected table. It then returns a value from a column to the right of that search column. It cannot directly return a value from a column to the left.

XLOOKUP lets you select the search range and result range independently. It means you can find an employee’s name using their ID, even when the ID column appears to the right of the name column. You can also use XLOOKUP to retrieve information from horizontally arranged data.

ii. Column References and Return Arrays

VLOOKUP uses a column index number to identify the result. For example, if the selected range is A2, column A is 1, column B is 2, and column C is 3. If you insert a column into the range, you may need to update the formula to ensure it returns the intended information.

XLOOKUP uses a separate return range instead of a column number. This makes the formula easier to read and maintain as datasets change.

iii. Exact Match and Approximate Match

XLOOKUP uses exact matching by default. You don’t need to specify an additional argument for common lookups such as finding a particular employee ID or product name.

VLOOKUP defaults to approximate matching if its fourth argument is omitted. For exact matches, add FALSE or 0 to the formula.

Approximate matching is useful for tasks such as assigning grades based on score ranges or finding tax brackets. However, it requires properly sorted data and careful formula setup. XLOOKUP also offers additional match options, including returning the next smaller or next larger value when an exact match isn’t available.

iv. Handling Missing Values and Errors

Both functions return #N/A when they cannot find an exact match and no alternative behavior is specified.

• With XLOOKUP, you can provide a custom message through the fourth argument:

=XLOOKUP(105, A2:A10, B2:B10, “ID not found”)

• VLOOKUP does not have a dedicated missing-value argument. You can combine it with functions such as IFNA to display a more useful message:

=IFNA(VLOOKUP(105, A2:B10, 2, FALSE), “ID not found”)

v. Search Order and Multiple Matching Results

VLOOKUP searches from the first row downward and returns the first matching result it encounters. XLOOKUP also returns the first match by default. However, its search_mode argument lets you search from the last item towards the first. This is useful when a dataset contains multiple entries for the same employee or product, and you want the most recently listed match.

For example, if an employee’s details appear in several records, you can use a reverse search to retrieve the last matching entry. However, this works as intended only when the data is arranged so the last occurrence is the record you need.

Neither function is designed to return every matching row in a single ordinary lookup. If you need all matching records, consider using FILTER in an Excel version that supports it.

Pro Tip: Want to strengthen your Excel skills and practice formulas hands-on? Explore the Internshala Advanced Excel Course to build practical skills.

LOOKUP vs. VLOOKUP vs. XLOOKUP

Excel has several functions for retrieving information, and their names can be confusing when you are starting. Here is a quick distinction between LOOKUP, VLOOKUP, and XLOOKUP:

• LOOKUP: Searches a single row or column and returns a value from the corresponding position in another row or column. It is best suited for approximate matching and requires appropriately sorted data.
• VLOOKUP: Searches vertically in the first column of a selected table and returns a value from a column to its right.
• XLOOKUP: Searches a specified range and returns a corresponding result from a separate range. It supports exact matching by default, reverse searches, and custom messages for missing values.

For most new spreadsheets, XLOOKUP is a flexible option when your Excel version supports it. VLOOKUP remains valuable for older workbooks and compatibility with older Excel versions.

When Should You Use XLOOKUP vs. VLOOKUP?

The right function depends on your Excel version, your data layout, and the result you need. Both functions are useful, but one may be easier to use for a particular task.

i. Choose XLOOKUP when…

XLOOKUP is a practical choice when you want a flexible, easy-to-read, and easy-to-update formula. Use it when:

• You need to return a value from a column to the left or right of the search range.
• You want exact matching without specifying an additional argument.
• You want to display a custom message when a value is missing.
• You need to search from the bottom of a list to find the last matching entry.
• You want a formula that uses separate search and result ranges instead of a column index.
• You need to retrieve multiple corresponding columns with a single formula in a supported Excel version.

ii. Choose VLOOKUP when…

VLOOKUP is useful when you are working with existing spreadsheets or need a formula compatible with older versions of Excel. Use it when:

• Your workbook already relies on VLOOKUP formulas.
• You need compatibility with Excel 2016 or Excel 2019.
• The search value is the first column in the selected range.
• The result is in a column to the right.
• You need a straightforward lookup in a simple table.

Conclusion

Choosing between XLOOKUP vs VLOOKUP depends on your spreadsheet and Excel version. XLOOKUP offers greater flexibility through separate search and return ranges, exact matching by default, and custom handling of missing values. VLOOKUP remains useful for basic lookups and older workbooks. Learning both functions gives you more options when managing data for assignments, internships, or workplace tasks. Start with the function that suits your current spreadsheet, then practice the other to see where it saves time.

Want to learn more Excel formulas and how to use them effectively? Explore our guide to Excel Formulas to learn the essentials and simplify your spreadsheet tasks.

FAQs