Lookup and Reference Functions

Lookup and reference functions in Excel are those functions that help us find our values in a sheet according to the criteria in the search. Here are some of the most used lookup and reference functions:



ADDRESS - Gets the address of a cell to specify the row and column numbers.

CHOOSE - Choose a value from a list of values based on an index.

COLUMN - Returns the column number of a reference.

COLUMNS - Returns the number of columns in an array or reference.

FORMULATEXT - Returns a formula as a string.

GETPIVOTDATA - Extract data stored in a PivotTable.

HLOOKUP - Looking for a specific value in the first row of a table array and returns in the same column a value of another row in the grid table.

HYPERLINK - Creates a shortcut or jump to a document saved on the hard drive, a network server or Internet.

INDEX - Returns a value or the reference to a value from within a table or range.

INDIRECT - Returns the reference specified by a text string.

MATCH - Searches for a value within an array and returns the relative position of the value in that array.

OFFSET - Returns a reference to a range that is a number of rows and columns from a cell or range of cells.

ROW - Returns the row number of a reference.

ROWS - Returns the number of rows in an array or reference.

TRANSPOSE - Converts a vertical range of cells in a horizontal range, or vice versa.

VLOOKUP - Looking for a specific value in the first column of a table array and returns in the same row a value of another column of the table array.