Excel Lookup and Reference Functions
FUNCTION SYNTAX
ADDRESS ADDRESS(row_num,column_num,abs_num,a1,sheet_text)
AREAS AREAS(reference)
CHOOSE CHOOSE(index_num,value1,value2,...)
CHOOSECOLS CHOOSECOLS(array,col_num1,[col_num2],…)
CHOOSEROWS CHOOSEROWS(array,row_num1,[row_num2],…)
COLUMN COLUMN(reference)
COLUMNS COLUMNS(array)
DROP DROP(array, rows,[columns])
FILTER FILTER(array, include, [if_empty])
FORMULATEXT FORMULATEXT(reference)
GETPIVOTDATA GETPIVOTDATA(data_field, pivot_table, field1,item1, field2,item2,...)
HLOOKUP HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
HSTACK HSTACK(array1,[array2],…)
HYPERLINK HYPERLINK(link_location,friendly_name)
INDEX INDEX(array,row_num,column_num)
INDIRECT INDIRECT(ref_text,a1)
LOOKUP LOOKUP(lookup_value,lookup_vector,result_vector)
MATCH MATCH(lookup_value,lookup_array,match_type)
MMULT MMULT(array1,array2)
OFFSET OFFSET(reference,rows,cols,height,width)
ROW ROW(reference)
TAKE TAKE(array, rows,[columns])
ROWS ROWS(array)
TRANSPOSE TRANSPOSE(array)
UNIQUE UNIQUE(array, [by_col], [exactly_once])
VLOOKUP VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
VSTACK VSTACK(array1,[array2],…)
WRAPROWS WRAPROWS(vector, wrap_count, [pad_with])
WRAPCOLS WRAPCOLS(vector, wrap_count, [pad_with])
XLOOKUP XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
XMATCH XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])