XMATCH in Zoho Sheet: Find row positions and last occurrences
Locate an order with Zoho Sheet XMATCH and pair its relative position with INDEX for flexible retrieval.
Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Zoho Sheet. How formula examples are checked
Quick answer: XMATCH gives the position within a range rather than a value from another column. This is useful when you want to locate a record, detect a repeated label or select a matching amount using INDEX.
This is a Zoho Sheet-specific tutorial, written against Zoho’s official online function reference checked on October 10, 2026. The vendor help does not identify a minimum Zoho application release for this function; a historical introduction date has not been verified. No example in this guide was run in a signed-in Zoho Sheet account. Expected outputs are independently checked against the supplied sample data.
Syntax and parameters
=XMATCH(search_item; search_region; [match_mode]; [search_mode])
| Parameter | Meaning |
|---|---|
search_item |
Lookup key, such as an order ID or an agent name. |
search_region |
Single row or column to search. |
match_mode |
0 exact by default; ±1 permit closest smaller/larger; 2 enables Zoho regex patterns. |
search_mode |
1 first-to-last, -1 last-to-first; ±2 perform binary search on an appropriately sorted region. |
Reproduce the example with real data
Download the eight-row CSV dataset and import it into a blank Zoho Sheet starting at cell A1; this creates a header on row 1 and data in rows 2–9. Leave the output area outside A1:F9. If IDs or numbers were changed during import, correct their data types before continuing.
These are representative records from the downloadable dataset (the formula ranges reference all eight records):
| Row | A: Order ID | B: Region | C: Status | D: Amount | E: Agent | F: Units |
|---|---|---|---|---|---|---|
| 2 | O-101 | East | Paid | 120 | Ana | 12 |
| 3 | O-102 | West | Pending | 75 | Bo | 5 |
| 6 | O-105 | West | Paid | 150 | Bo | 3 |
| 8 | O-107 | West | Paid | 125 | Ana | 12 |
Enter the formula in a vacant cell, for example H2:
=XMATCH("O-105";A2:A9;0;1)
Expected result: 5.
Walkthrough: The first cell of A2:A9 is position 1. O-105 is at sheet row 6, which is position 5 in the selected range—not worksheet row number 6.
A second example
=XMATCH("Bo";E2:E9;0;-1)
Expected result: 5. Bo occurs in E3 and E6. A reverse search selects E6, which is position 5 relative to E2:E9.
More advanced use
=INDEX(D2:D9;XMATCH("O-107";A2:A9;0;1);1)
Expected result: 125. XMATCH finds O-107 at position 7; INDEX returns the amount from the seventh cell in D2:D9, which is 125.
A last-occurrence search is useful for histories where the most recent item is at the bottom. Before depending on reverse search, confirm that the table is actually appended in chronological order; XMATCH does not inspect timestamps itself.
Common errors and troubleshooting
- Relative position is not the same thing as worksheet row number. Add the range’s starting row offset only when you truly need a sheet row.
- Use a one-dimensional search range; a two-dimensional block can make match semantics ambiguous.
- Do not enable binary search (search_mode 2 or -2) until the lookup region is sorted in the required direction.
- Zoho match_mode 2 uses regex-style matching. A copied Excel wildcard expression can behave differently.
Compatibility and version notes
XMATCH and XLOOKUP are related, but XMATCH returns a numeric offset while XLOOKUP returns an aligned value. Pairing XMATCH with INDEX works in other modern spreadsheet applications too, subject to syntax, support, and pattern-mode differences.
The examples deliberately follow Zoho’s semicolon-separated syntax. Spreadsheet software may use different argument separators based on product and locale. This guide covers the current documented Zoho function, not an unverified assertion about support in older releases.
Related Zoho Sheet tutorials
Official reference and verification
Verification status: Official documentation checked; example outcomes were independently reasoned or arithmetically checked where applicable. Not executed in Zoho Sheet.
- No genuine Zoho Sheet screenshots are included; this guide does not use mock application images.