Blog

XLOOKUP vs VLOOKUP: Which is best?

XLOOKUP VS VLOOKUP

If you work with Excel, you have probably used the VLOOKUP function to look up values in a table based on a given criteria. However, you may have also encountered some limitations and frustrations with this function, such as having to specify the column number, or only being able to look at columns to the right. Fortunately, there is a new function that can overcome these drawbacks: the XLOOKUP function.

The XLOOKUP function was introduced in Excel 365 in 2019 and is designed to replace both the VLOOKUP and HLOOKUP functions. It has a simpler syntax and more flexibility than its predecessors. In this article, we will compare the XLOOKUP and VLOOKUP functions and see how they differ in terms of advantages and disadvantages.

How to use XLOOKUP and VLOOKUP

Before we compare the two functions, let’s review how to use them. Both functions are used to look up a value in a table based on a given criterion, but they have different syntaxes.

The syntax of the VLOOKUP function is:

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

Where:

  • lookup_value is the value you want to find in the first column of the table.
  • table_array is the range of cells that contains the table.
  • col_index_num is the column number in the table from which you want to return a value.
  • range_lookup is an optional argument that specifies whether you want an exact match or an approximate match. If you omit this argument or set it to TRUE, Excel will use an approximate match. If you set it to FALSE, Excel will use an exact match.

The syntax of the XLOOKUP function is:

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

Where:

  • lookup_value is the value you want to find in the lookup array.
  • lookup_array is the range of cells that contains the values you want to look up.
  • return_array is the range of cells that contains the values you want to return.
  • if_not_found is an optional argument that specifies what you want Excel to return if it cannot find a match. If you omit this argument or set it to #N/A, Excel will return #N/A. You can also enter any text or value you want as an alternative.
  • match_mode is an optional argument that specifies how you want Excel to match the lookup value. If you omit this argument or set it to 0, Excel will use an exact match. You can also use other values to specify different types of matches, such as wildcard matches or case-sensitive matches.
  • search_mode is an optional argument that specifies how you want Excel to search for the lookup value. If you omit this argument or set it to 1, Excel will search from first to last. You can also use other values to specify different search modes, such as binary search or reverse search.

The advantages of XLOOKUP over VLOOKUP

The XLOOKUP function has several advantages over the VLOOKUP function that make it more powerful and versatile. Here are some of them:

  • XLOOKUP can look up values in any column of the table, not just the first one. This means you don’t have to rearrange your data or use helper columns to use XLOOKUP.
  • XLOOKUP can return values from any column of the table, not just one. This means you can use XLOOKUP to return multiple values at once by using an array as the return_array argument.
  • XLOOKUP can look up values in any direction, not just vertically. This means you can use XLOOKUP to perform horizontal lookups as well as vertical ones by switching the lookup_array and return_array arguments.
  • XLOOKUP can handle errors and blanks more gracefully than VLOOKUP. This means you can use the if_not_found argument to specify what you want Excel to return if it cannot find a match, such as a default value or a custom message. You can also use the match_mode argument to specify how you want Excel to handle wildcard characters or case sensitivity.
  • XLOOKUP can perform faster and more efficient searches than VLOOKUP. This means you can use the search_mode argument to specify how you want Excel to search for the lookup value, such as using a binary search for sorted data or a reverse search for the last match.

The disadvantages of XLOOKUP over VLOOKUP

The XLOOKUP function is not perfect and has some disadvantages compared to the VLOOKUP function. Here are some of them:

  • XLOOKUP is not compatible with older versions of Excel. This means you can only use XLOOKUP if you have Excel 365 or later. If you need to share your workbook with someone who has an older version of Excel, you will have to use VLOOKUP or another alternative function.
  • XLOOKUP may require more arguments than VLOOKUP. This means you may have to enter more information to use XLOOKUP than VLOOKUP, depending on your situation. For example, if you want to perform an approximate match with XLOOKUP, you will have to specify the match_mode argument, whereas with VLOOKUP, you can simply omit the range_lookup argument.

Conclusion

The XLOOKUP function is a powerful and versatile function that can replace both the VLOOKUP and HLOOKUP functions in Excel. It has a simpler syntax and more flexibility than its predecessors. It can look up values in any column and direction, return multiple values at once, handle errors and blanks more gracefully, and perform faster and more efficient searches. However, it also has some drawbacks, such as requiring more arguments than VLOOKUP in some cases. Even though XLOOKUP is the stronger function overall, VLOOKUP is still a very powerful and useful function, so it’s worth knowing how to use both of them.