lookup functions
find a value in a range and return a related one.
| function | signature | what it does | tests |
|---|---|---|---|
| ADDRESS | ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) | returns a cell address as text from row and column numbers | — |
| AREAS | AREAS(reference) | returns the number of areas in a reference | 2 |
| HLOOKUP | HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) | searches first row and returns matching value | 2 |
| INDEX | INDEX(array, row_num, [column_num], [area_num]) | returns value at row and column in range | 3 |
| INDIRECT | INDIRECT(ref_text, [a1]) | returns reference specified by text string | 1 |
| LOOKUP | LOOKUP(lookup_value, lookup_vector, [result_vector]) | looks up value in one-row or one-column range | 1 |
| MATCH | MATCH(lookup_value, lookup_array, [match_type]) | returns position of value in a range | 13 |
| OFFSET | OFFSET(reference, rows, cols, [height], [width]) | returns reference offset from starting point | 1 |
| ROW | ROW([reference]) | returns row number of a reference | 4 |
| ROWS | ROWS(array) | returns number of rows in a reference | 2 |
| VLOOKUP | VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | looks up value in first column of range and returns value from specified column | 2 |
| XLOOKUP | XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) | searches range and returns matching item | — |
| XMATCH | XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) | returns the position of an item in a range or array | — |