Lookup & Reference Functions¶

VLOOKUP¶

Searches the first column of a range and returns a value from a specified column.

=VLOOKUP(lookup_value, table_range, col_index, [is_sorted])

=VLOOKUP("Apple", A1:C10, 3, FALSE) -- finds "Apple" in column A and returns the value from column C.

Same as VLOOKUP but searches the first row and returns from a specified row.

=HLOOKUP(lookup_value, table_range, row_index, [is_sorted])

=HLOOKUP("Q1", A1:D3, 2, FALSE)

Modern replacement for VLOOKUP/HLOOKUP. Searches a range and returns a corresponding value.

=XLOOKUP(lookup_value, lookup_range, return_range, [not_found], [match_mode])

=XLOOKUP("Apple", A1:A10, C1:C10, "N/A")

INDEX returns a value from a range by row/column position. MATCH returns the position of a value in a range.

=INDEX(range, row_num, [col_num])
=MATCH(lookup_value, lookup_range, [match_type])

Combined: =INDEX(B1:B10, MATCH("Apple", A1:A10, 0)) -- equivalent to VLOOKUP but more flexible.

Function	Syntax	Example	Description
OFFSET	`OFFSET(ref, rows, cols, [height], [width])`	`=OFFSET(A1, 2, 1)`	Reference offset from a starting cell
INDIRECT	`INDIRECT(ref_text)`	`=INDIRECT("A"&B1)`	Converts text to a cell reference
ROW	`ROW([reference])`	`=ROW(A5)`	Returns row number (5)
COLUMN	`COLUMN([reference])`	`=COLUMN(C1)`	Returns column number (3)
ROWS	`ROWS(range)`	`=ROWS(A1:A10)`	Number of rows in range (10)
COLUMNS	`COLUMNS(range)`	`=COLUMNS(A1:C1)`	Number of columns in range (3)
CHOOSE	`CHOOSE(index, val1, val2, ...)`	`=CHOOSE(2, "a", "b", "c")`	Returns "b" (2nd value)
ADDRESS	`ADDRESS(row, col, [abs_type])`	`=ADDRESS(1, 3)`	Returns "$C$1"