VLOOKUP

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 TRUE, matching Excel and LibreOffice.
• When range_lookup=TRUE, approximate match logic is used against the first column.
• In exact mode, a blank candidate never matches. A blank lookup value matches a real numeric zero, but not blank, text, or boolean candidates.
• Approximate matching assumes the first column is sorted ascending. Unsorted data is detected and returns #N/A rather than a silently wrong row, matching LibreOffice Calc (#283).
• Numeric range_lookup values use logical coercion: zero is exact and nonzero is approximate.
• 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

Related functions

• HLOOKUP
• XLOOKUP
• MATCH
• CHOOSE
• CHOOSECOLS
• CHOOSEROWS

FAQ

What is the default behavior when range_lookup is omitted?

VLOOKUP defaults range_lookup to TRUE, so it performs approximate matching; pass FALSE or 0 for exact matching.

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