Lookup & reference functions
The 37 lookup & reference functions in Sheets, with the usual names, arguments and results.
| Function | What it does | Syntax |
|---|---|---|
| ADDRESS | Cell address as text | ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) |
| AREAS | Number of areas in a reference | AREAS(reference) |
| CHOOSE | Pick the Nth value | CHOOSE(index, value1, value2, ...) |
| CHOOSECOLS | Selected columns of an array | CHOOSECOLS(array, col_num1, [col_num2, ...]) |
| CHOOSEROWS | Selected rows of an array | CHOOSEROWS(array, row_num1, [row_num2, ...]) |
| COLUMN | Column number of a reference | COLUMN([reference]) |
| COLUMNS | Number of columns in a range | COLUMNS(array) |
| DROP | Array without its first or last rows/columns | DROP(array, rows, [columns]) |
| EXPAND | Pad an array to a given size | EXPAND(array, rows, [columns], [pad_with]) |
| FILTERexample | Rows or columns that meet a condition | FILTER(array, include, [if_empty]) |
| FORMULATEXT | Formula of a cell as text | FORMULATEXT(reference) |
| GROUPBY | Group rows and aggregate their values | GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship]) |
| HLOOKUP | Look up across the first row | HLOOKUP(lookup, table, row_index, [approximate]) |
| HSTACK | Append arrays side by side | HSTACK(array1, [array2, ...]) |
| HYPERLINK | Link shown as its friendly name | HYPERLINK(link_location, [friendly_name]) |
| INDEXexample | Value at a row/column of a range | INDEX(range, row, [column]) |
| INDIRECT | Reference given as text | INDIRECT(ref_text, [a1]) |
| LOOKUP | Approximate lookup in sorted data | LOOKUP(lookup_value, lookup_vector, [result_vector]) |
| MATCHexample | Position of a value in a range | MATCH(lookup, range, [match_type]) |
| OFFSET | Reference moved from a starting cell | OFFSET(reference, rows, cols, [height], [width]) |
| PIVOTBY | Aggregate values by row and column groups | PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to]) |
| ROW | Row number of a reference | ROW([reference]) |
| ROWS | Number of rows in a range | ROWS(array) |
| SORTexample | Sort the rows or columns of an array | SORT(array, [sort_index], [sort_order], [by_col]) |
| SORTBY | Sort an array by other arrays | SORTBY(array, by_array1, [sort_order1], ...) |
| TAKE | First or last rows and columns of an array | TAKE(array, rows, [columns]) |
| TOCOL | Array as a single column | TOCOL(array, [ignore], [scan_by_column]) |
| TOROW | Array as a single row | TOROW(array, [ignore], [scan_by_column]) |
| TRANSPOSE | Swap the rows and columns of an array | TRANSPOSE(array) |
| TRIMRANGE | Range without its outer blank rows/columns | TRIMRANGE(range, [trim_rows], [trim_cols]) |
| UNIQUEexample | Distinct rows or columns of an array | UNIQUE(array, [by_col], [exactly_once]) |
| VLOOKUPexample | Look up down the first column | VLOOKUP(lookup, table, col_index, [approximate]) |
| VSTACK | Append arrays one below the other | VSTACK(array1, [array2, ...]) |
| WRAPCOLS | Wrap a row or column into columns | WRAPCOLS(vector, wrap_count, [pad_with]) |
| WRAPROWS | Wrap a row or column into rows | WRAPROWS(vector, wrap_count, [pad_with]) |
| XLOOKUPexample | Look up a value, return the matching item | XLOOKUP(lookup, lookup_range, return_range, [if_not_found], [match_mode], [search_mode]) |
| XMATCHexample | Position of an item in a row or column | XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) |