Excel Function Translator Logo
    The Excel Translator

    XLOOKUP

    Lookup & Reference

    Modern replacement for VLOOKUP. Searches a range and returns a matching item. More flexible and powerful.

    Translations
    EnglishXLOOKUP
    FrenchRECHERCHEX
    SpanishBUSCARX
    GermanXVERWEIS
    ItalianCERCA.X
    PortuguesePROCX
    DutchX.ZOEKEN
    PolishX.WYSZUKAJ
    RussianПРОСМОТРX
    TurkishXARA
    CzechXLOOKUP
    HungarianXKERES
    SwedishLETAX
    DanishXOPSLAG
    FinnishXHAKU
    Syntax
    XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

    Arguments

    • lookup_valueThe value to search for
    • lookup_arrayThe range to search in
    • return_arrayThe range to return from
    • if_not_foundValue to return if not found(optional)
    • match_mode0=exact, -1=exact or next smaller, 1=exact or next larger, 2=wildcard(optional)
    • search_mode1=first-to-last, -1=last-to-first, 2=binary ascending, -2=binary descending(optional)
    Examples
    =XLOOKUP(A1,B:B,C:C)
    Matched value

    Basic lookup returning from column C

    =XLOOKUP(A1,B:B,C:C,"Not found")
    Value or 'Not found'

    With error handling

    =XLOOKUP(A1,B:B,C:E)
    Multiple columns

    Returns entire row

    Tips & Best Practices
    • •Available in Excel 365 and Excel 2021+
    • •Can search from right to left
    • •Can return multiple columns at once
    • •Has built-in error handling
    Common Mistakes
    • •Building a workbook around XLOOKUP and then sharing it with someone on Excel 2019 or earlier, who just sees #NAME? and no explanation why
    • •Skipping if_not_found and getting a raw #N/A instead of a message that actually tells the user what happened
    • •Letting lookup_array and return_array end up different sizes, which throws a #VALUE! error
    Related Functions
    VLOOKUPThe function XLOOKUP is meant to replace - still worth knowing since so many workbooks still use it
    INDEXPair with MATCH for the same flexibility on Excel versions that don't have XLOOKUP yet
    MATCHTeams up with INDEX as the pre-365 workaround for everything XLOOKUP does
    XMATCHHands back the position instead of the value, useful when the row number is what you actually need
    Frequently Asked Questions

    Why does my XLOOKUP formula show #NAME??

    Because XLOOKUP only exists in Excel 365 and Excel 2021+. Open that file in Excel 2019, 2016, or an older Mac version, and Excel has no idea what the function is. INDEX/MATCH is the compatible fallback.

    Does XLOOKUP cover both VLOOKUP and HLOOKUP?

    Yes. Point lookup_array vertically or horizontally and XLOOKUP handles either case, so you genuinely only need to learn one function.

    What's the cleanest way to avoid #N/A with XLOOKUP?

    Use if_not_found directly in the formula, like XLOOKUP(A1,B:B,C:C,"Not found"), instead of bolting IFERROR on top of the whole thing.

    Need to translate a formula using XLOOKUP?

    Use our translator to convert your complete formula