XLOOKUP
Searches range and returns matching item.
- Excel: Not checked yet
- Google Sheets: Not checked yet
- Returns: Any value
Examples
=XLOOKUP("beta", C1:C5, A1:A5) | A | B | C | |
|---|---|---|---|
| 1 | 10 | alpha | |
| 2 | 20 | beta | |
| 3 | 30 | gamma | |
| 4 | 40 | delta | |
| 5 | 50 | epsilon |
Result 20
=XLOOKUP("zeta", C1:C5, A1:A5, "not found") Result, over the same cells not found
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])lookup_value- any required
lookup_array- range required
return_array- range required
if_not_found- any optional
match_mode- number optional
search_mode- number optional
Related
ADDRESSReturns a cell address as text from row and column numbers. AREASReturns the number of areas in a reference. HLOOKUPSearches first row and returns matching value. HYPERLINKThe text a link displays: clickability is the renderer's. INDEXReturns value at row and column in range. INDIRECTReturns reference specified by text string.