LOOKUP
The LOOKUP function finds a match for a given search value in one collection, and then returns the value in the cell with the same relative position in a second collection.
Tip: For an enhanced search that works in any direction, use XLOOKUP.
LOOKUP(search-for, search-where, result-values)
search-where: The collection containing the values to be searched. search-where can contain any values.
result-values: An optional collection containing the value to be returned based on the search. result-values can contain any values.
Notes
Both search-where and result-values are normally included and are one-dimensional — that is, either multiple columns or multiple rows, but not both. However, for compatibility with other spreadsheet apps, search-where can be specified as two-dimensional — both multiple columns and multiple rows — and result-values can be omitted.
If search-where is two-dimensional and result-values is specified, the topmost row or leftmost column, whichever contains more cells, is searched and the corresponding value from result-values is returned.
If search-where is two-dimensional and result-values is omitted, the corresponding value in the last row (if the number of columns included in the collection is larger) or column (if the number of rows included in the collection is larger) is returned.
Examples |
|---|
Given the following table: |
A | B | C | D | E | |
|---|---|---|---|---|---|
1 | 0 | 20 | 40 | 60 | 80 |
2 | A | E | I | O | U |
3 | vel | elit | dolor | sit | amet |
4 | 1 | 2 | 3 | 4 | 5 |
=LOOKUP(20, A1:E1, A2:E2) returns “E”, because “E” is in the same position (second cell) in the result-values collection (A2:E2) as 20 (search-for) is in the search-where collection (A1:E1). =LOOKUP(“E”, B1:B4, A4:D4) returns 2, because “E” is the second value in the search-where collection and 2 is the second value in the search-for collection. Note that one collection was a column while the other was a row, but that they were of equal size. =LOOKUP(20, A1:E2, A4:E4) returns 2, because 20 is in the second cell of the top row of the search-where collection (the topmost row is used) and 2 is in the second cell of the search-where collection. =LOOKUP(“vel”, A1:B4, B2:E2) returns “O”, because “vel” is in the third cell of the first column of the search-where collection (the leftmost column is used) and “O” is in the third cell of the search-where collection. =LOOKUP(“vel”, A1:B4, B2:E4) returns an error because the result-values collection can’t be more than one row or one column wide. =LOOKUP(100, A1:E1, A4:E4) returns 5, because 100 is greater than the last (fifth) cell specified in the search-where collection and 5 is in the fifth cell of the specified result-values collection. =LOOKUP(REGEX("^a.*"), A3:E3, A1:E1) returns 80, the value in A1:E1 that corresponds to the first value in A3:E3 which starts with an “a”.B |