Description
Searches a range or an array then returns the item corresponding to the first match it finds.
NOTES
- Allows exact or approximate search (expected results if the array is sorted).
- XLOOKUP is case-insensitive function, supports text search with wildcards or regular expressions.
- Allows searching from the beginning and ending positions of an array.
- By concatenating multiple columns as search criteria, you can search by multiple conditions in some cases.
Syntax
Copied!=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
SAMPLE data
| | A | B | C | D | E | F |
|:---:|:----|:--------|------:|:----|:----|:----|
| 1 | | | | | | |
|-----|-----|---------|-------|-----|-----|-----|
| 2 | | Product | Count | | | |
|-----|-----|---------|-------|-----|-----|-----|
| 3 | | Bananas | 25 | | | |
|-----|-----|---------|-------|-----|-----|-----|
| 4 | | Oranges | 28 | | | |
|-----|-----|---------|-------|-----|-----|-----|
| 5 | | Apples | 38 | | | |
|-----|-----|---------|-------|-----|-----|-----|
| 6 | | Pears | 41 | | | |
|-----|-----|---------|-------|-----|-----|-----|
| 7 | | | | | | |
|-----|-----|---------|-------|-----|-----|-----|EXAMPLE 1
Copied!//𝘰𝘱𝘵𝘪𝘰𝘯𝘢𝘭, 𝘪𝘧_𝘯𝘰𝘵_𝘧𝘰𝘶𝘯𝘥=#𝘕/𝘈, 𝘮𝘢𝘵𝘤𝘩_𝘮𝘰𝘥𝘦=0 [-1,0,1,2], search_mode=1 [-2,-1,1,2]. //𝘳𝘦𝘵𝘶𝘳𝘯𝘴 𝘵𝘩𝘦 𝘲𝘶𝘢𝘯𝘵𝘪𝘵𝘺 𝘰𝘧 𝘢 𝘱𝘳𝘰𝘥𝘶𝘤𝘵 𝘸𝘪𝘵𝘩 𝘢 5-𝘤𝘩𝘢𝘳𝘢𝘤𝘵𝘦𝘳 𝘯𝘢𝘮𝘦. =XLOOKUP("^\w{5}$", B3:B6, C3:C6) -> 𝘳𝘦𝘵𝘶𝘳𝘯 38
example 2
Copied!//𝘵𝘢𝘬𝘦 𝘰𝘶𝘵 𝘵𝘩𝘦 𝘱𝘳𝘰𝘥𝘶𝘤𝘵 𝘸𝘪𝘵𝘩 𝘵𝘩𝘦 𝘩𝘪𝘨𝘩𝘦𝘴𝘵 𝘲𝘶𝘢𝘯𝘵𝘪𝘵𝘺 𝘯𝘰𝘵 𝘦𝘹𝘤𝘦𝘦𝘥𝘪𝘯𝘨 45. =XLOOKUP(45, C3:C6, B3:B6, , -1) -> 𝘳𝘦𝘵𝘶𝘳𝘯 '𝘗𝘦𝘢𝘳𝘴'