XLOOKUP

XLOOKUP is a relatively new function that was introduced in Excel 2019. It's similar to VLOOKUP, but it has a few critical advantages. For one, XLOOKUP can look up values in a range of cells, not just in a single column, and it can also lookup values to the left. This is handy if you have data spread 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. If users don't specify this argument, and no matches are found, then the function returns the #N/A error. When users specify an invalid search mode (E.g. - 0 in Excel), then the #VALUE error will be returned.
  • [match_mode] - [Optional] Specifies the type of the match as per the following values:
    • 0 - Refers to an exact match. If no matches are found, then the #N/A error is returned. This is the default match mode.
    • -1 - Refers to an exact match. If no matches are found, then the next smaller item is returned.
    • 1 - Refers to an exact match. If no matches are found, then the next larger item is returned.
    • 2 - Refers to a wildcard match where "*", "?" and "~" characters possess special meanings to indicate a partial match.
  • [search_mode] - [Optional] Specifies the mode of the search as per the following values:
    • 0 - Refers to the "search all" mode where all the matched values will be returned. [This mode is not available in Excel.]
    • 1 - Refers to a search that starts at the first item. This is the default search mode.
    • -1 - Refers to a reverse search that starts at the last item.
    • 2 - Refers to a binary search that depends upon the lookup_array argument being sorted in ascending order.
    • -2 - Refers to a binary search that depends upon the lookup_array argument being sorted in descending order.
  • Not_found - [Optional] This argument can be used to override the #N/A error. Typical values for not_found might be: "Not found", "No match", "No result", etc.

Examples

The following GIF shows how to use XLOOKUP to find the total amount spent for a product for a particular last name.

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

Conclusion

If you're looking for a more powerful alternative to VLOOKUP, XLOOKUP is a great option. It has a few key advantages, including the ability to look up values in a range of cells, lookup values multi-directionally, return an approximate match, and return multiple values. Sign-up for the beta today to get started using XLOOKUP in Sourcetable.