Formualizer Docs
ReferenceFunctionsLookup Functions

VLOOKUP

VLOOKUP: Looks up a value in the first column of a table and returns a value from another column.

Summary

VLOOKUP searches vertically and returns the matching row's value from col_index_num.

Remarks

  • col_index_num is 1-based and must be within the table width.
  • range_lookup defaults to FALSE in this engine (exact match by default).
  • When range_lookup=TRUE, approximate match logic is used against the first column.
  • If the lookup value is not found, returns #N/A.
  • If col_index_num is invalid, returns #REF! (or #VALUE! if non-numeric).
  • A matched empty target cell is materialized as numeric 0.

Examples

Exact match in a key/value table
Grid
CellValue
Formula
=
Result
Not evaluated yet.
Expected
18
Approximate tier lookup
Grid
CellValue
Formula
=
Result
Not evaluated yet.
Expected
Silver

FAQ

What is the default behavior when range_lookup is omitted?

This engine defaults range_lookup to FALSE, so VLOOKUP performs exact matching unless TRUE is explicitly provided.

What happens if col_index_num points outside the table?

A numeric out-of-range column index returns #REF!, while a non-numeric col_index_num returns #VALUE!.

Runtime metadata

Category

Lookup

Signature

VLOOKUP(arg1: Any, arg2: Any (Range), arg3: Number, arg4?: Logical)

Arity

min 3, max 4

Arguments

arg1

Any · Scalar

arg2

Any · Range

arg3

Number · Scalar · coercion NumberStrict

arg4optionaldefault

Logical · Scalar · coercion Logical

Caps

PURELOOKUP

Source

crates/formualizer-eval/src/builtins/lookup/core.rs

UNIQUE

UNIQUE: Returns distinct rows or columns from a range.

VSTACK

VSTACK: Concatenates arrays vertically into a single spilled array.

On this page

SummaryRemarksExamplesRelated functionsFAQWhat is the default behavior when range_lookup is omitted?What happens if col_index_num points outside the table?Runtime metadata