Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Arguments
| Argument | Required | Description |
|---|---|---|
lookup_value | Required | The value to look for |
lookup_array | Required | The row or column to search |
return_array | Required | The range or array to return from |
if_not_found | Optional | What to return when nothing matches (default #N/A) |
match_mode | Optional | 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard |
search_mode | Optional | 1 first to last (default), -1 last to first, 2 binary search ascending, -2 binary search descending |
How to use XLOOKUP
XLOOKUP takes 3 required arguments and 3 optional arguments. Type =XLOOKUP( in a cell and SheetsBat shows the arguments as you go. Optional arguments are shown in square brackets; leave them out to use their default.
Examples
Each result below was calculated by the SheetsBat formula engine.
=XLOOKUP("b", {"a";"b";"c"}, {10;20;30})Result =XLOOKUP("z", {"a";"b"}, {1;2}, "none")Result =XLOOKUP(25, {10;20;30}, {"x";"y";"z"}, , -1)Result Related lookup and reference functions
- INDIRECTReturns the reference written in a text value, such as "B3" or "Sheet2!A1:A10".
- LOOKUPFinds a value in a sorted row or column and returns the value at the same position in another row or column.
- MATCHReturns the position of a value in a row or column.
- OFFSETReturns a reference shifted a number of rows and columns from a starting reference, optionally with a new size.
- ROWReturns the row number of a reference, or of the cell with the formula when left out.
- ROWSReturns the number of rows in a range or array.
- TRANSPOSESwaps the rows and columns of a range or array.
- VLOOKUPLooks for a value in the first column of a table and returns the value in the same row from another column.
- XMATCHReturns the position of an item in a row or column, with exact, approximate, wildcard and binary search options.