Lookup
XMATCH
Definition
XMATCH returns the relative position of a value in a row or column.
Syntax
=XMATCH(lookup_value, lookup_array)
Arguments
| Argument | Meaning |
|---|---|
lookup_value |
The value to find, such as a word in quotes or a cell reference. |
lookup_array |
A single row or column to search, such as A2:A4. |
Worked example
A grocery list is stored in A2:A4, and you want to know where Pear sits in it.
| A: Fruit |
|---|
| Fig |
| Pear |
| Lime |
Formula: =XMATCH("Pear",A2:A4)
Result: 2. Pear is the second entry inside the range A2:A4.
Practice
Range A2:A4 holds red, blue, and green. In B2, write a formula that returns the position of blue.
- In B2, write an XMATCH formula that returns the position of blue in A2:A4.
- B2 should show the position of blue in A2:A4.
Step 1 of 2
Enter =XMATCH("blue",A2:A4) in B2.
blue is the second item, so the position is 2.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
B2 uses XMATCH.
B2 now shows 2.
Related functions
MATCH is the older position search. XLOOKUP can return the matching value instead of the position.
Common mistake
Reading the result as a sheet row: XMATCH("Pear",A2:A4) is 2, the second cell in the range, not row 3 of the sheet.