VLOOKUP

VLOOKUP searches for a value in the first column of a table and returns the corresponding value from another column in the same row. Use it to reference structured data — like looking up product prices, employee names, or customer IDs — based on a known key.

Syntax

VLOOKUP(lookup_value, table, column_index, [exact_match], [case_sensitive])

Parameters

• lookup_value (Any, required) — The value to search for in the first column of the table. Use (question) REFERENCES when the lookup_value is dynamic.

• table (Table, required) — The table containing lookup data

• column_index (Number, required) — The 1-based index of the column from which to return a value

• exact_match (Boolean, optional) — If true, finds an exact match; if false, finds the closest match. Defaults to true

• case_sensitive (Boolean, optional) — Whether the lookup should treat uppercase and lowercase as different. Defaults to false

Returns

Any — the value from the specified column of the row where a match was found.

Simple Example

DATA := TABLE(
  ARRAY("Apple", 2.5),
  ARRAY("Banana", 1.8),
  ARRAY("Cherry", 3.0)
)

VLOOKUP("Banana", DATA, 2)  // 1.8

Business Examples

Retrieve a product price based on name:

VLOOKUP("Pro Plan", PRICING_TABLE, 2)  
// returns the price for the "Pro Plan" product

Find the volume price in your pricing table:

VLOOKUP(QTY, PRICING_TABLE, COLORS)  
// returns the price for the the quantity (row) and number of colors (column) in a pricing table.

Find an employee's department using their ID:

VLOOKUP(EMP123, EMPLOYEE_TABLE, 3)  
// returns the department for employee ID 123

Match a customer's region for segmented reports:

VLOOKUP("CUST-458", CUSTOMERS, 4, true)  
// finds the region field for that customer record

Common Mistakes & Tips

• Returns NULL or error if the lookup value isn't found

• For reverse lookups or horizontal data, use XLOOKUP (when available)

See Also

• XLOOKUP — More flexible lookup function

• INDEX — Retrieve values by position

• ROW — Get row information

• COLUMN — Get column information

• TABLE — Create structured tables