Excel formula library

Excel Tutorial

Every Excel formula in one place — search, filter by category or level, then open syntax, samples, and pitfalls. New formulas added to the library appear here automatically.

Start with first formula Browse formulas
127 Formulas
11 Categories
3 Skill levels
Live Search & filters

Formula list

Find any Excel formula

Filter by category — Math, Lookup, FILTER and other Dynamic Arrays, Text, Date, and more — then open a formula for syntax and examples.

Math & Arithmetic Statistical Analysis Logical & Conditional Lookup & Reference Date & Time Text & Strings Information & Arrays Dynamic Arrays (365) Enhanced Lookup (365) Advanced Calculation (365) Text & Reshaping (365)

Chapter 9

Enhanced Lookup (365)

  1. 107 XLOOKUP =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) Searches a range or an array and returns an item corresponding to the first match found. Defaults to exact match, allows leftward lookups, and has built-in error handling. Enhanced Lookup (365) Beginner Office 365

    Syntax

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

    Arguments

    lookup_value
    The value to search for.
    lookup_array
    The array or range to search within.
    return_array
    The array or range to return values from (can be left of lookup_array!).
    [if_not_found]
    Value returned if no match is found (eliminates need for IFERROR).

    Sample

    =XLOOKUP(C2, B2:B6, A2:A6, "Missing")

    Result Rohan Sharma

    Sample data

    Download
    ABCD
    2Rohan SharmaE-101E-101Rohan Sharma
    3Priya SinghE-102E-104Neha Kapoor
    4Amit VermaE-103E-999Missing (No Error!)
    Pro tip

    To find the most recent transaction date or last log entry, set search_mode to -1 (searches bottom to top).

    Common pitfall

    lookup_array and return_array must be of identical dimension length (e.g. both must span rows 2 through 100); otherwise, #VALUE! is returned.

  2. 108 XMATCH =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) Enhanced Lookup (365) Intermediate Office 365

    Syntax

    =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

    Arguments

    lookup_value
    The value you are looking for.
    lookup_array
    The range or array being searched.
    [match_mode]
    0 (exact match - default), -1 (exact or next smaller), 1 (exact or next larger), 2 (wildcard).

    Sample

    =XMATCH("Amit Verma", A2:A6)

    Result 3

    Sample data

    Download
    Pro tip

    Unlike old MATCH(val, range, 0), XMATCH defaults to exact match automatically, saving you from typing ,0 every single time.

    Common pitfall

    If lookup_value is not found and no error handler is applied, XMATCH returns #N/A.