Lookup and reference functions - worksheet


Lookup and reference functions are grouped under the Lookup and reference category in the Excel function library, and the Insert Function dialog box. Argument descriptions vary between array, number, and value. Here is a list of the more commonly used functions.


Excel - all versions


Lookup and Reference functions
- a selection
Description
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text]) Returns the address of a cell in a worksheet, given specified row and column numbers.
COLUMN([reference]) Returns the column number of the given cell reference, or the left most column of a multi-column range
COLUMNS(array) Returns the number of columns in an array or reference
FORMULATEXT(reference) Returns the cell formula as a string, or #N/A if reference is not a formula
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) Returns the value of the indicated cell by looking in the top row of an array
INDEX(array, row_num, [column_num]) Returns the value from a reference or array from its index row and column coordinates
MATCH(lookup_value, lookup_array, [match_type])T Returns a value from a single column or single row reference or array.
OFFSET(reference, rows, cols, [height], [width];) Returns a reference offset from an anchor reference - single cell, or multi-cell.
ROW(reference) Returns the row number of the given cell reference, or the top most row of a multi-column ranges
ROWS(reference) Returns the number of rows in an array or reference.
TRANSPOSE(array) Returns the transpose of an array
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) Returns the value of the indicated cell by looking in the left column of an array


Excel - Office 365 version


Lookup and Reference functions
- a selection
Description
FILTER(array, include, [if_empty]) Returns a filtered range based on a specified criteria
SORT(array, [sort_index], [sort_order], [by_col]) Returns the sorted contents of an array or range
SORTBY(array, by_array1], [sort_order], …) Returns the sorted contents of an array or range based on values in a corresponding array
UNIQUE(array, [by_col], [exactly_once]) Returns a list of unique values in a list or range
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [search_mode]) Returns an item corresponding to the first match found, based on the search item. If a match doesn't exist, then the closest (approximate) match is returned
XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]) Returns the relative position of an item in an array or range of cells.