docs > vlookup vs xlookup

VLOOKUP vs XLOOKUP

If you work with spreadsheets, you've probably used the VLOOKUP function at some point. Since its creation in 1985, it has been one of the most widely used formulas in Excel for its ability to look up data vertically to the right. This function allows you to look up a value in a table based on specific criteria. For example, you could use VLOOKUP to find the name of a customer based on their customer ID. But did you know there's another function called XLOOKUP that can do even more? First, let’s take a look at VLOOKUP.

VLOOKUP

This formula allows you to find a value from the left column and gives you a value in the same row from the column number that you choose in the formula to the right. It is widely used by finance and other business professionals for its ability to match data in a large data set. VLOOKUP can search for numeric or string data and returns either numeric or string data.

Syntax

The structure of the formula is as follows:
=VLOOKUP(lookup_value, table_array, col_index_num, approx)

  • lookup_value - the leftmost value to use for searching the array
  • table_array - the cell range that has the data that you want to search
  • col_index_num - the column for the value that you want
  • approx - Boolean (True or False) value for getting an approximate match or an exact match; if no value is provided that default will be TRUE for an approximate match

Examples

=VLOOKUP(F3,A2:C98,3,FALSE)

VLOOKUP gif for total spent by customer

XLOOKUP

XLOOKUP is a relatively new function that was introduced in Excel 2019. It's similar to VLOOKUP, but it has a few important advantages. For one, XLOOKUP can look up values in a range of cells, not just in a single column, and it can look up values to the left as well. This is handy if you have data that's spread out across multiple columns. Another advantage of XLOOKUP is that it can return an approximate match. This can be useful if your data isn't sorted in exactly the right way. Finally, XLOOKUP can return more than one value. This can be handy if you need to return multiple pieces of information about a single item. By default XLOOKUP returns an exact match. Important things to note when working with XLOOKUP:

  • The #N/A error is used if the lookup value is not located.

  • If the return range/array dimensions aren’t compatible with the look_up array then the formula will show a VALUE! Error.

  • The formula accepts numeric and string data and returns numeric and string data, including multiple items

Syntax

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])This function has the following arguments:

  • lookup_value - the value to use for searching the array
  • lookup_array - the range to find the lookup_value
  • return_array - the range to find the value to return
  • [if_not_found] - [Optional] Refers to the value to be returned when no match is found.
  • [match_mode] - [Optional] Specifies the type of the match
  • [search_mode] - [Optional] Specifies the mode of the search
  • Not_found - [Optional] This argument can be used to override the #N/A error.

    Examples

    XLOOKUP_GIF

    The following GIF illustrates the use of SUM and XLOOKUP to sum the total spent for a range of people:

    XLOOKUP_SUM_GIF

    Whether you need to find one piece of data or many VLOOKUP and XLOOKUP are there to help!

Drop CSV