Excel function category
Excel Lookup and Reference Functions
Reference pages for lookup and reference formulas that find values, locate positions, and work with ranges in practical spreadsheets.
14 functions
CHOOSE
Lookup & Reference
Return one value from a list by position number.
Syntax
CHOOSE(index_num, value1, [value2], ...)COLUMN
Lookup & Reference
Return the column number of a reference or of the cell that contains the formula.
Syntax
COLUMN([reference])FILTER
Lookup & Reference
Return only the rows or columns in an array that meet criteria you define.
Syntax
FILTER(array, include, [if_empty])HLOOKUP
Lookup & Reference
Search across the top row of a table and return a value from a specified row underneath it.
Syntax
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])INDEX
Lookup & Reference
Return a value from a range by row number, column number, or both.
Syntax
INDEX(array, row_num, [column_num])INDIRECT
Lookup & Reference
Return the reference specified by a text string, allowing dynamic cell and range references.
Syntax
INDIRECT(ref_text, [a1])MATCH
Lookup & Reference
Return the relative position of a value inside a row or column.
Syntax
MATCH(lookup_value, lookup_array, [match_type])OFFSET
Lookup & Reference
Return a reference shifted by a specified number of rows and columns from a starting point.
Syntax
OFFSET(reference, rows, cols, [height], [width])ROW
Lookup & Reference
Return the row number of a reference or of the cell that contains the formula.
Syntax
ROW([reference])SORT
Lookup & Reference
Sort the contents of a range or array by one of its rows or columns.
Syntax
SORT(array, [sort_index], [sort_order], [by_col])SORTBY
Lookup & Reference
Sort a range or array based on values in a separate range or array.
Syntax
SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)UNIQUE
Lookup & Reference
Return a list of distinct values from a range or array.
Syntax
UNIQUE(array, [by_col], [exactly_once])VLOOKUP
Lookup & Reference
Search for a value in the first column of a table and return a value from the same row.
Syntax
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])XLOOKUP
Lookup & Reference
Search a range or array for a match and return a corresponding item from a second range or array.
Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])