Browse

XLOOKUP in Zoho Sheet: Exact, reverse and regex matching

Spreadsheet Intermediate Zoho Sheet

Retrieve order values with Zoho Sheet XLOOKUP; learn reverse search, missing-value defaults, and regex differences from Excel.

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: When order IDs and amounts live in separate columns, XLOOKUP returns the matching value directly without counting columns. Zoho Sheet also documents search direction and regex matching, which makes this function useful for repeated agents or partial names.

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

=XLOOKUP(lookup_value; search_table; result_table; [if_not_found]; [match_mode]; [search_mode])
Parameter Meaning
lookup_value The ID or label being searched.
search_table One row or column of lookup keys.
result_table Aligned values to return; keep the rows/columns matching the search_table size.
if_not_found Optional fallback for no matching value.
match_mode 0 exact (default); -1 exact or next smaller; 1 exact or next larger; 2 regex-style match.
search_mode 1 forwards (default), -1 backwards, ±2 binary searches with properly sorted data.

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:

=XLOOKUP("O-105";A2:A9;D2:D9;"Order absent")

Expected result: 150.

Walkthrough: O-105 is the fifth data record; the aligned amount in D6 is 150. The optional fallback is unused because an exact match exists.

A second example

=XLOOKUP("O-999";A2:A9;D2:D9;"Order absent")

Expected result: Order absent. The practice dataset has no such ID, so the supplied not-found message is returned instead of leaving a lookup error.

More advanced use

=XLOOKUP("Bo";E2:E9;D2:D9;"No match";0;-1)

Expected result: 150. Bo appears twice. Searching from the bottom encounters row 6 (O-105, amount 150) before row 3 (O-102, amount 75), so this is a deliberate last-match lookup.

If all you need is a fixed exact ID match, leave off the match/search modes and provide an explicit if_not_found string. Save reverse searches for cases where a duplicate key is meaningful, such as wanting the last recorded allocation.

Common errors and troubleshooting

  • Keep search_table and result_table aligned. A shifted return range can produce a plausible but incorrect amount.
  • Search modes 2 and -2 require correctly sorted source keys; choosing a binary mode on unsorted data can give incorrect results.
  • Zoho match_mode 2 is documented as regex-style pattern matching using .*, .? and / escapes. Excel’s match_mode 2 uses wildcards *, ? and ~, so rewrite patterns when switching programs.
  • An empty result from a matched row differs from the no-match fallback. Check blanks in return values separately.

Compatibility and version notes

Zoho’s published XLOOKUP regex mode is not equivalent to the wildcard mode described in Microsoft’s XLOOKUP help. The three base arguments and exact-lookup use case are similar, but the special-character rules and locale separators must be reviewed when porting formulas.

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.

Microsoft documentation for Excel XLOOKUP confirms that Excel’s match_mode 2 uses wildcard matching, whereas Zoho documents regex-style matching for its mode 2.

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.