XLOOKUP

Formulas / XLOOKUP
Find values in a range or array using XLOOKUP.
=XLOOKUP (lookup_value, table_array, col_index_num, [range_lookup], [search_mode], [if_not_found])
  • lookup - the lookup value
  • lookup_array - the array or range to search
  • return_array - the array or range to return
  • not_found [OPTIONAL] - the value to return if no match is found
  • match_mode [OPTIONAL]
  • search_mode [OPTIONAL]

Examples

  • =XLOOKUP(H4,B5:B9,E5:E9)

    The function performs a basic exact match to retrieve sales data on a movie. This example searches the range B5:B9 for the value in cell H4, and then returns the corresponding value from the range E5:E9.

  • =XLOOKUP(E5,B5:B9,C5:C9,,-1)

    The function performs a basic approximate match by finding an exact or next smallest match. This example searches the range B5:B9 for the value in cell E5, and then returns the corresponding value from the range C5:C9, or the next smallest value if an exact match isn't found.

  • =XLOOKUP(B5,B8:B15,C8:E15)

    The function can return more than one value at the same time. This example searches the range B8:B15 for the value in cell B5, and then returns the corresponding value from the ranges C8:E15. As multiple ranges are used, up to three values can be returned.

  • =XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

    The function performs a two way lookup by nesting one XLOOKUP within another XLOOKUP. This example searches the range C4:F4 for the value in cell I6. The value that is returned is determined by a second lookup which searches the range B5:B9 for the value in cell I5, and then returns the corresponding value from the range C5:F9.


Summary

XLOOKUP is a Sourcetable function that enables users to quickly locate items within a table or range by searching by a designated column. If an exact match is not found, an approximate match is returned.

  • XLOOKUP supports approximate and exact matching, wildcards, partial matches, and can return data in vertical or horizontal ranges.
  • XLOOKUP can start searching from the first or last value.
  • XLOOKUP supports an optional not_found argument and can return "Not found" when no match is found or a #N/A error if no match is found or an empty string is used for not_found.

Frequently Asked Questions

What is the purpose of the XLOOKUP function?
The XLOOKUP function is used to find items in a table or range by column. It can also return the closest approximate match if no exact match is found.
Can I use the XLOOKUP function with a workbook created in Excel 2016 or 2019?
Yes, the XLOOKUP function can be used with a workbook created in Excel 2016 or 2019.
What happens if no exact match is found when using the XLOOKUP function?
If no exact match is found when using the XLOOKUP function, it will return the closest approximate match.
What is the syntax for the XLOOKUP function?
The syntax for the XLOOKUP function is:
  • XLOOKUP (lookup_value, lookup_array, return_array [optional], [if_not_found], [match_mode], [search_mode])

Related Functions

Sourcetable Logo

Work smarter

Al is here to help. Leverage the latest models to
analyze spreadsheets, enrich data, and create reports.

Drop CSV