Spread Formula Reference
Formula Functions / Functions T to Z / XLOOKUP
In This Topic
    XLOOKUP
    In This Topic

    XLOOKUP performs lookups in vertical and horizontal cell ranges and provides support for approximate matching, partial matching (using widcard characters like * , ? etc.) and exact matching. By default, the XLOOKUP function returns the exact matched results.

    The XLOOKUP function is comparatively more flexible and powerful than the rest of the lookup functions (like VLOOKUP, HLOOKUP and LOOKUP etc.). Some more benefits of using XLOOKUP function instead of other lookup functions are listed below:

    For instance - By using the XLOOKUP function, users can determine the price of a product by its product ID, find a matching tax rate in a column, search an employee name based on their employee ID and perform many other search operations while working with spreadsheets.

    Syntax

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

    Arguments

    This function has the following arguments:

    Argument Description
    lookup_value Refers to the lookup value.
    lookup_array Refers to the lookup array or cell range that you want to search.
    return_array Refers to the array or cell range that you want to return.
    [match_mode]

    [Optional] Specifies the type of the match as per the following values:

    0 -  Refers to exact match. If no matches are found, then #N/A error is returned. This is the default match mode.

    -1 - Refers to exact match. If no matches are found, then the next smaller item is returned.

    1 -  Refers to 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 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 the ascending order.

    -2 -  Refers to a binary search that depends upon the lookup_array argument being sorted in the descending order.

    Remarks

    The following points must be kept in mind while working with the XLOOKUP function in the spreadsheets:

    Data Types

    Accepts numeric data. Looks up values in a range or table. Returns an array with multiple items.

    Examples

    XLOOKUP(F2,B2:B11,D2:D11)

    XLOOKUP(E2,C2:C7,B2:B7,1,1)

    XLOOKUP(B2,B5:B14,C5:D14)

    Version Available

    This function is available in product version 13.0 or later.

    See Also

    HLOOKUP | LOOKUP | Lookup Functions