Lookup & Reference functions in Excel

Hello all. In this section, let’s look into all the Lookup & Reference functions available in Excel. The below table lists out the function and its usage / syntax.

Function Usage Syntax
ADDRESS Returns the address of the cell based on row & column number =address(row_num,col_num,[abs_num],[a1],[sheet])
AREAS Returns the number of areas in a reference. Area can be range or a single cell =areas(reference)
CHOOSE Returns a value from a list, based on an index number =choose(index_num, value1,[value2],…)
COLUMN Returns a column number of a reference =column(reference)
COLUMNS Returns the number of columns in an array or reference =columns(array)
FIELDVALUE Returns a value from a field =fieldvalue(value, field_name)
FILTER Filters a range or an array =filter(array,include,[if_empty])
FORMULATEXT  Returns a formula from the cell  =formulatext(reference)
GETPIVOTDATA  Retrieve data from a pivot table =getpivotdata(data_field,pivot_table,[field1,item1])
HLOOKUP  Lookup a value in a table by referencing the first row =hlookup(lookup_value,table_array,row_index_num,[range_lookup])
HYPERLINK  Creates a clickable shortcut for the cell / value =hyperlink(link_location,[friendly_name])
INDEX  Retrieve the value from a list or table based on the specified location =index(reference, row_num, [column_num],[area_num])
INDIRECT  Returns the reference from the specified string =indirect(ref_text,[a1])
LOOKUP  Lookup a value in an array or one-row range or one-column range =lookup(lookup_value,lookup_vector,[result_vector])
MATCH  Returns the relative position of an item in the array that matches the specified value =match(lookup_value,lookup_array,[match_type])
OFFSET  Returns a specific value or a range that is a specified number of rows and columns from a reference cell  =offset(reference, rows, columns, [height], [width])
ROW  Returns the row number for a reference  =row(reference)
ROWS Returns the number of rows in an array or reference =rows(array)
RTD  Retrieves real time data from a program =RTD(progID,server, topic1,[topic2],..)
SORT  Sorts a range or an array =sort(array, [sort_index], [sort_order], [by_col])
SORTBY  Sorts a range or an array based on specified values  =sortby(array,by_array,[sort_order1],..)
TRANSPOSE  Changes the orientation of a range  =transpose(array)
UNIQUE  Returns unique values from range / array  =unique(array,[by_col],[exactly_once])
VLOOKUP  Lookup a value in a table by referencing the first column  =vlookup(lookup_value,table_array,col_index_num,[range_lookup])
XLOOKUP  Searches the range or an array for a match and return the appropriate value from the 2nd array  =xlookup(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
XMATCH  Returns the relative position of a value in an array =xmatch(lookup_value,lookup_array, [match_mode],[search_mode])
I hope you liked this one pager on all the lookup and reference functions in excel. Please provide us with your valuable feedback that would help to improve the content. Thanks for reading.

Leave a Reply

%d bloggers like this: