Description
Searches an element and returns its relative position in the given array or reference.
NOTES
- Allows exact or approximate search (expected results if the array is sorted).
- XMATCH 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!=XMATCH(lookup_value, lookup_array, [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 𝘤𝘩𝘢𝘳𝘢𝘤𝘵𝘦𝘳𝘴. =XMATCH("^\w{5}$", B3:B6, 3) -> 𝘳𝘦𝘵𝘶𝘳𝘯 4
example 2
Copied!//𝘵𝘢𝘬𝘦 𝘰𝘶𝘵 𝘵𝘩𝘦 𝘱𝘳𝘰𝘥𝘶𝘤𝘵 𝘸𝘪𝘵𝘩 𝘵𝘩𝘦 𝘩𝘪𝘨𝘩𝘦𝘴𝘵 𝘲𝘶𝘢𝘯𝘵𝘪𝘵𝘺 𝘯𝘰𝘵 𝘦𝘹𝘤𝘦𝘦𝘥𝘪𝘯𝘨 45. =INDEX(B3:B6, XMATCH(45, C3:C6, -1)) -> 𝘳𝘦𝘵𝘶𝘳𝘯 '𝘗𝘦𝘢𝘳𝘴'