Browse

VLOOKUP in Zoho Sheet: Exact order lookups and match modes

Spreadsheet Intermediate Zoho Sheet

Find an amount by order ID with Zoho Sheet VLOOKUP and understand its unusual exact and regex-capable match modes.

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: You have an order ID in an email and need the amount recorded in a spreadsheet. A lookup prevents manually scanning a long list and reduces the risk of reading an adjacent row.

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

=VLOOKUP(lookup_value; data_table; column_index; [mode])
Parameter Meaning
lookup_value ID or value to search for in the first column of data_table.
data_table The entire lookup range, with key column at the left.
column_index One-based position of the return column within data_table.
mode Match rule. Zoho documents 2 as exact only, 0 as exact/regex-capable, and sorted modes for approximate lookup.

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
5 O-104 North Paid 90 Cy 0
6 O-105 West Paid 150 Bo 3
9 O-108 North Pending 80 Cy 4

Enter the formula in a vacant cell, for example H2:

=VLOOKUP("O-104";A2:F9;4;2)

Expected result: 90.

Walkthrough: O-104 is on sheet row 5. Within A:F, column 4 is D (Amount), which contains 90. Explicit 2 requests exact-only matching in Zoho.

A second example

=VLOOKUP("O-108";A2:F9;5;2)

Expected result: Cy. Column 5 of A:F is Agent, so the last order returns Cy, not its amount.

More advanced use

=IFERROR(VLOOKUP("O-999";$A$2:$F$9;4;2);"Unknown order")

Expected result: Unknown order. The missing key is converted to a warning. The dollar signs make the lookup table stable if a formula is copied.

For new tables, XLOOKUP is easier to maintain because its return range is separate and a newly inserted field does not change a numeric column_index. Keep VLOOKUP for compatibility with existing workbooks or policies that depend on a fixed table range.

Common errors and troubleshooting

  • The searched ID must be in the first column of the chosen table; VLOOKUP cannot directly return a value from a column to the left.
  • Count column_index from the table range, not from the worksheet. Starting at B changes every positional number.
  • Do not substitute Excel’s fourth-argument conventions. Excel documents 0/FALSE for exact lookup, while Zoho describes mode 2 as exact-only and mode 0 as exact/regex-capable.
  • For approximate modes, check the required sort direction first. Using an unsorted list risks silently inaccurate matches.

Compatibility and version notes

Portability warning: In Excel, =VLOOKUP(...,2) uses a nonzero fourth argument and is treated as approximate matching. The same 2 has a different documented meaning in Zoho Sheet. Adjust the fourth argument explicitly when migrating 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 VLOOKUP documents a TRUE/FALSE matching argument. Zoho’s documented mode numbers are different; revise formulas before migration.

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.