Excel Lookup and Reference Functions

Excel comes with multiple Lookup and Reference functions that let you find matching values. Use this article to decide which function is best for your needs.

FunctionPurposeParam,Optional
ADDRESS Create a cell address from a given row and column row_num col_numabs_num a1 sheet
AREAS Get the number of areas in a reference. reference
CHOOSE Get a value from a list based on position index_num value1value2 ...
COLUMN Get the column number of a reference. reference
COLUMNS Get the number of columns in an array or reference. array
FORMULATEXT Get the formula in a cell reference
HLOOKUP Look up a value in a table by matching on the first row value table row_indexrange_lookup
HYPERLINK Create a clickable link. link_locationfriendly_name
INDEX Get a value in a list or table based on location array row_numcol_num area_num
INDIRECT Create a reference from text ref_texta1
LOOKUP Look up a value in a one-column range lookup_value lookup_vectorresult_vector
MATCH Get the position of an item in an array lookup_value lookup_arraymatch_type
OFFSET Create a reference offset from given starting point reference rows colsheight width
ROW Get the row number of a reference reference
ROWS How to use the Excel ROWS function to Get the number of rows in an array or reference. array
TRANSPOSE Flip the orientation of a range of cells array
VLOOKUP Lookup a value in a table by matching on the first column value table col_indexrange_lookup