Excel functions / Lookup

LOOKUP function

Finds a value in a sorted row or column and returns the value in the same place in another. XLOOKUP is the modern choice.

Syntax

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Arguments

When to use it

LOOKUP searches a sorted row or column for a value and gives back the item in the same position of a second one. When the exact value is not there, it uses the largest one below it, which is what you want for bands: grades from scores, a discount from an order size.

It is the oldest of the lookup functions. For an exact match use XLOOKUP or VLOOKUP with FALSE.

Examples

  1. A grade from a score

    ABCDEF
    1FromGradeScore
    20F72B
    350C
    470B
    590A

    In F2: =LOOKUP(D2, A2:A5, B2:B5) → B

    72 is not in the list, so LOOKUP uses 70, the largest value below it.

    Try this example

  2. The last number in a column

    ABCD
    1DayReading
    2Mon1412
    3Tue17
    4Wed
    5Thu12

    In D2: =LOOKUP(9.99E+307, B2:B5) → 12

    No number is as large as 9.99E+307, so LOOKUP ends on the last number there is.

    Try this example

Common mistakes

LOOKUP in other languages

Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.

Translate a whole formula

Try LOOKUP in the playground

Related functions