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. |
- This example was developed in Excel for Office 365 ProPlus 64 bit.
- Published: 31 March 2020
- Revised: Saturday 25th of February 2023 - 10:13 AM, [Australian Eastern Standard Time (EST)]