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).
- MATCH is case-insensitive function, supports wildcards when searching for text.
- By concatenating multiple columns as search criteria, you can search by multiple conditions in some cases.
Syntax
Copied!=MATCH(lookup_value, lookup_array, [match_type])
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!//𝘰𝘱𝘵𝘪𝘰𝘯𝘢𝘭, 𝘮𝘢𝘵𝘤𝘩_𝘵𝘺𝘱𝘦=1 [0-2] //𝘧𝘪𝘯𝘥 𝘵𝘩𝘦 𝘱𝘳𝘰𝘥𝘶𝘤𝘵 𝘸𝘩𝘰𝘴𝘦 𝘲𝘶𝘢𝘯𝘵𝘪𝘵𝘺 𝘪𝘴 41. =INDEX(B2:B6, MATCH(41, C3:C6, 0)) -> 𝘳𝘦𝘵𝘶𝘳𝘯 '𝘗𝘦𝘢𝘳𝘴'
example 2
Copied!//𝘨𝘦𝘵 𝘢 𝘱𝘳𝘰𝘥𝘶𝘤𝘵 𝘯𝘢𝘮𝘦 𝘵𝘩𝘢𝘵 𝘴𝘵𝘢𝘳𝘵𝘴 𝘸𝘪𝘵𝘩 𝘵𝘩𝘦 𝘭𝘦𝘵𝘵𝘦𝘳 𝘗. =MATCH("A*", B3:B6, 0) -> 𝘳𝘦𝘵𝘶𝘳𝘯 '𝘈𝘱𝘱𝘭𝘦𝘴'