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
lookup_value: What to find.lookup_vector: One row or column to search, sorted smallest to largest.result_vector(optional): The row or column to return from. Left out, the answer comes from the last row or column of the first range.
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
A grade from a score
A B C D E F 1 From Grade Score 2 0 F 72 B 3 50 C 4 70 B 5 90 A In F2:
=LOOKUP(D2, A2:A5, B2:B5)→ B72 is not in the list, so LOOKUP uses 70, the largest value below it.
The last number in a column
A B C D 1 Day Reading 2 Mon 14 12 3 Tue 17 4 Wed 5 Thu 12 In D2:
=LOOKUP(9.99E+307, B2:B5)→ 12No number is as large as 9.99E+307, so LOOKUP ends on the last number there is.
Common mistakes
- A list that is not sorted. LOOKUP expects the list in ascending order. If it is not, the result can be wrong, with no error to warn you.
- Expecting an error when the value is missing. LOOKUP never insists on an exact match. A code that is not in the list quietly returns the row before where it would be.
- A value smaller than everything in the list. There is nothing below it to use, so the result is #N/A.
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.
- Czech: VYHLEDAT
- Danish: SLÅ.OP
- Dutch: ZOEKEN
- Finnish: HAKU
- French: RECHERCHE
- German: VERWEIS
- Hungarian: KERES
- Italian: CERCA
- Norwegian: SLÅ.OPP
- Polish: WYSZUKAJ
- Portuguese (Brazil): PROC
- Portuguese (Portugal): PROC
- Russian: ПРОСМОТР
- Spanish: BUSCAR
- Swedish: LETAUPP
- Turkish: ARA
Related functions
- XLOOKUP: Searches a range and returns the matching item from another range. The modern replacement for VLOOKUP.
- VLOOKUP: Finds a value in the first column of a table and returns a value from the same row.
- MATCH: The position of a value in a row or column.
- INDEX: The value at a given row and column of a range.
- IFS: Checks conditions in order and returns the value for the first TRUE one.