XLOOKUP function
Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Square brackets indicate optional arguments.
Arguments
| Argument | Required | Meaning |
|---|---|---|
| lookup_value | Yes | The value to search for, If omitted, XLOOKUP returns blank cells it finds in lookup_array. A1 |
| lookup_array | Yes | The array or range to searchA1:A20 |
| return_array | Yes | The array or range to returnB1:B20 |
| if_not_found | No | Where a valid match is not found, return the [if_not_found] text you supply. If a valid match is not found, and [if_not_found] is missing, #N/A is returned.default |
| match_mode | No | Specify the match type: 0 - Exact match. If none found, return #N/A. This is the default. -1 - Exact match. If none found, return the next smaller item. 1 - Exact match. If none found, return the next larger item. 2 - A wildcard match where *, ?, and ~ have special meaning.0 |
| search_mode | No | Specify the search mode to use: 1 - Perform a search starting at the first item. This is the default. -1 - Perform a reverse search starting at the last item. 2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. -2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.1 |
Try XLOOKUP
Return the last literal matching record
=XLOOKUP(D2,A2:A4,B2:B4,"Missing",0,-1)Reverse exact lookup returns the value beside the last case-insensitive A* ID. A wildcard interpretation would answer a different question.
Inspect the current formula
Read its written arguments or observe actual operation values in a separate local calculation. This action neither applies a draft nor grades an answer.
Read the formula, one argument at a time
Explore the written function calls and their inputs. The calculation result belongs to the complete formula.
XLOOKUP · top-level call
=XLOOKUP(D2,A2:A4,B2:B4,"Missing",0,-1)1 / 6 · lookup_value
The value to search for, If omitted, XLOOKUP returns blank cells it finds in lookup_array.
XLOOKUP reference ↗Follow the cells behind a result
Read explicit local references and helper formulas using a separate calculation. Your draft stays in this field; the worksheet and practice checks do not change.
The worksheet runs locally. Published checks apply to the supplied example only. Native Excel and Google Sheets compatibility remain unverified.
Practice across worksheets
Build a linked lookup or conditional report, edit its source sheets and check the formula against other datasets.
Open cross-sheet formula practice →Practice a complete task
Reference and compatibility
Univer 1.0.4 / Apache-2.0. Calculation checks cover the supplied examples only. The browser worksheet does not implement every Excel feature, external service or connected data type.