XLOOKUP
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.
Lookup & ReferenceBeginner EssentialsLookup & Matching EssentialsModern Excel Functions
What it does
Searches a range for a match and returns the corresponding value from another range.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Arguments
lookup_value(value) — The value to search for.lookup_array(range) — The range or array to search.return_array(range) — The range or array to return from.
Examples
Exact match lookup
Return Dept for an ID using an input cell.
FORMULA
=XLOOKUP(A2,B2:B5,C2:C5,"Not found")
RESULT
Sales
SAMPLE DATA
| A | B | C | |
|---|---|---|---|
| 1 | Lookup ID | ID | Dept |
| 2 | 1002 | 1001 | Ops |
| 3 | 1002 | Sales | |
| 4 | 1003 | HR | |
| 5 | 1004 | IT |