Search for a specific value in a range and return its position.
| Scenario | Formula | Remarks |
|---|---|---|
| Find the position of "Orange" in the list. | =XMATCH("Orange", A2:A6) | |
| Find the position of "apple" (even if written as "APPLE" or "apple") | =XMATCH("apple", A2:A6, 0) | |
| Match with Next Larger Value | =XMATCH(55, A2:A6, 1) | Find the position of 55 in {10, 20, 40, 60, 80}. If 55 is not found, returns next larger |
| Match with Next Smaller Value | =XMATCH(55, A2:A6, -1) | Find the position of 55 in {10, 20, 40, 60, 80}. If 55 is not found then returns next smaller |
| Wildcard Match | =XMATCH("App*", A2:A10, 2) | |
| Find the Row Number in a Table | =XMATCH("John", A2:A100) + 1 | |
| Find Position Using Dynamic Arrays | =XMATCH("Mango", A2:A10#) |