XLOOKUP vs VLOOKUP in Excel: differences and advantages

1. Where to search (lookup array vs. table array)

VLOOKUP forces the user to indicate the entire table_array, searching for the lookup value exclusively in the leftmost column. The column to return a value from is determined by the column number in the subsequent argument.

XLOOKUP, in contrast, allows the lookup and return columns to be specified separately. This separation ensures exceptional flexibility - the lookup column can be positioned to the left or right of the return value column.

2. Where to return a value from (return array vs. column number)

VLOOKUP employs a hardcoded index number to denote the return column, creating vulnerability when altering the lookup array structure. Changes like adding or removing columns may easily break your formula.

With XLOOKUP, you supply a range reference for the return array, making it better at handling changes in your source data. Moreover, XLOOKUP can accommodate multiple columns in the return array, a feature requiring two or more different VLOOKUP formulas to achieve.

3. How to match (match mode vs. range lookup)

The range_lookup argument in VLOOKUP and match_mode in XLOOKUP determine whether the formula seeks an exact or approximate match. While both functions share similar possibilities, XLOOKUP defaults to an exact match, offering a more intuitive default setting. Additionally, XLOOKUP provides an extra option for an approximate match returning the next larger value, without requiring the lookup array to be sorted, as VLOOKUP does.

4. How to handle missing values (if not found)

In cases where the lookup value is not found, VLOOKUP can only return the standard #N/A! error, potentially causing you to question the formula's accuracy.

By providing the if_not_found argument, XLOOKUP enables you to return a personalized message, such as "The value you are looking for is not found in the database".

Simply put, while VLOOKUP can only say "I can't find it" with an error, XLOOKUP lets you display something specific if it can't find what you're looking for. For example:

=XLOOKUP(F10, B4:B25, C4:C25, "Oops, nothing is found :(")

5. How to search (search mode)

VLOOKUP always starts searching at the top and stops when it finds the first match.

XLOOKUP introduces the search_mode argument, allowing users to do both first-to-last and last-to-first searches. Also, it lets you leverage binary search methods for faster results in extensive datasets.

For example, in the table below, VLOOKUP can only find the supplier for the first order of a given item, while XLOOKUP can do it for both the first and last orders. Just set search_mode to 1 for the first match and -1 for the last match:

=XLOOKUP(J4, F4:F25, G4:G25, , , -1)

In summary, XLOOKUP gives more choices and control allowing us to specify different arrays for the lookup and return values, as well as various options for the match mode, search mode, and error handling.

1. Position of lookup column (left lookup)

While VLOOKUP is limited to searching only in the first column in the specified table_array, XLOOKUP can look up values in any column, not just the leftmost one. This means XLOOKUP can easily perform bi-directional lookups without needing any data rearrangement.

As you can notice in the screenshot below, VLOOKUP fails to bring a value from a column (Item) when it's on the right side of the lookup column (Supplier).

=VLOOKUP(E4, A4:B25, 1, FALSE)

XLOOKUP, on the other hand, handles this left-side lookup with ease:

=XLOOKUP(E4, B4:B25, A4:A25)

2. Return values from multiple columns

VLOOKUP can only return a single value at a time, limiting its efficiency. On the contrary, XLOOKUP can return values from multiple columns in one go. This means you can use a single XLOOKUP formula instead of multiple VLOOKUPs.

For example, if you want to retrieve all details about a particular item, using VLOOKUP requires four separate formulas. You'd supply col_index_num values ranging from 2 (for Region in column B) to 5 (for Supplier in column E):

To get the region:

=VLOOKUP($B$17, $A$4:$E$13, 2, FALSE)

To get the discount:

=VLOOKUP($B$17, $A$4:$E$13, 3, FALSE)

Due to support for dynamic arrays, XLOOKUP simplifies this process, allowing you to fetch all the data with just one formula:

=XLOOKUP(B17, A4:A13, B4:E13)

3. Search with multiple criteria

XLOOKUP shines at searching for values using multiple criteria without needing extra helper columns, unlike VLOOKUP. For practical examples, please check out:

• XLOOKUP formula with multiple criteria
• VLOOKUP with multiple criteria

4. Finding the last occurrence

To locate the last occurrence using VLOOKUP, you would need to reverse the order of your source data. However, with the search_mode parameter, XLOOKUP can get the last instance of the lookup value without any extra moves. For practical examples, please see:

• XLOOKUP to get the last match

5. Column insertions or deletions

VLOOKUP relies on a static index column number, making it vulnerable to issues when updating the data source or making changes to the column order. XLOOKUP handles such modifications easily, ensuring the formula remains intact.

6. Size limit for the lookup value

In VLOOKUP, the length of the lookup criteria cannot exceed 255 characters, or you'll get a #VALUE! error. But XLOOKUP imposes no limit on the lookup value’s size. So, if your dataset contains really long strings, XLOOKUP is the solution to avoid errors.

7. Sorting of lookup range

While VLOOKUP demands ascending order sorting for its approximate search, XLOOKUP doesn’t have this limitation, providing adaptability in scenarios where sorting is not possible.

8. Versatility in matching

XLOOKUP stands out for its versatility in matching options offering various choices, such as exact matches, two types of approximate matching (next smaller or next larger), wildcard matches, and even binary searches. In contrast, VLOOKUP has fewer options, limited to exact matches and next smaller in approximate searches.

9. Handling errors

XLOOKUP is better at handling errors or missing values. You can decide what it should show if it can't find the requested value, which VLOOKUP doesn't allow.

10. Efficiency and speed

XLOOKUP is faster and more efficient because it looks only where it needs to (the lookup array and the return array), without wasting time and resources on scanning the entire table like VLOOKUP does.

11. Ease of use

While VLOOKUP has fewer arguments, simplicity in syntax doesn't necessarily equate to ease of use. XLOOKUP’s parameters, such as the lookup array and return array, offer a more intuitive approach compared to VLOOKUP's table array and column index number. This makes XLOOKUP more user-friendly, especially for beginners.

12. Compatibility with Excel versions

This is the only area where VLOOKUP surpasses its counterpart :) While VLOOKUP operates in any version of Excel, XLOOKUP is available only in Microsoft 365, Excel 2021, and Excel for the Web.

These differences highlight XLOOKUP's dynamic and efficient nature, making it a powerful choice over VLOOKUP for various data lookup tasks in Excel.

1. Search in any row

HLOOKUP can only search in the top row of the table_array, while XLOOKUP can search in any row.

2. Search in both directions

HLOOKUP can only go from left to right, but XLOOKUP can search in both directions. It's like having a double-sided search tool.

3. Matching types and defaults

HLOOKUP defaults to an approximate match and needs the lookup row to be sorted ascending. On the flip side, XLOOKUP defaults to an exact match, which is what you'd usually need in most situations.

All the other differences we talked about between XLOOKUP and VLOOKUP also apply to HLOOKUP. So, XLOOKUP is like a super function in Excel that can find and return values from any table or range with more flexibility, control and features than both older lookup functions combined.