Photo of magnifying glass.

What is XLookup?

/ Tutorials / By

What is XLookup? XLookup is the latest, most flexible and fastest performing lookup function available in Excel. It is only available with a Microsoft 365 subscription, however and will not be added to older versions of Excel. While in many ways XLookup is similar to older lookup functions like VLookup and HLookup, it has some clear advantages.

XLookup searches a range or an array for a lookup value, and returns results based on the first full or partial match that it finds. Organize the data table in either a by-row or by-column format, it doesn’t matter. And unlike other lookup functions, XLookup can return a single value or an array.

If you are using an older version of Excel, you can have many of the benefits of XLookup by using a combination of Index and Match.

XLookup Syntax

XLookup([Lookup Value], [Search Array], [Return Array], [Default Result], [Match Mode], [Search Mode])

  • Lookup Value – Required. This is the value to search for. It can be a number, or a string of text.
  • Search Array – Required. The function searches this array for the Lookup Value. The Search Array can either be a row or a column of a worksheet.
  • Return Array – Required. The search returns a result from this array. The Return Array can be either rows or columns, but it must correspond to the Search Array.
  • Default Result – Optional. You can specify text to be returned when a valid match is not found. If Default Result is missing, a #N/A error will be returned.
  • Match Mode – Optional. Optionally, you can specify what kind of match you want:
    • 0: Exact Match. This is the default. If no match is found, return the Default Result, or #N/A error.
    • -1: If an exact match is not found, return the next smaller item. This is how VLookup and HLookup work if Approximate Match is enabled.
    • 1: If an exact match is not found, return the next larger item.
    • 2: A wildcard match. We’ll go deeper into this topic in the examples.
  • Search Mode – Optional. Optionally, you can specify the search mode to use:
    • 1: Perform a search starting at the first item. This is the default
    • -1: Perform a reverse search starting at the last item.
    • 2: Perform a binary search. For this mode to work properly, the Search Array must be sorted in ascending order.
    • -2: Perform a binary search. For this mode to work properly, the Search Array must be sorted in descending order.

Examples

XLookup is a robust function. I’ve organized examples of its use into three separate articles:

  • Using XLookup with a by-row data table
  • Using XLookup with a by-column data table
  • Nesting XLookup functions to perform two-way lookups

For background on data table organization, and other lookup and reference functions in Excel, see the article Lookup Functions in Excel.

If you need to be compatible with older versions of Excel, see the article Combining Index with Match in Excel.

Privacy Policy