Excel Lookup and Reference Functions

This page lists the current, built-in Excel Lookup and Reference Functions. These functions are provided by Excel, to help you to work with arrays of data. They include functions that return the location of a given address or value, and functions to look up given values.

The functions listed below have been grouped into categories, to help you to find the function you need to perform a specific task. If you select a function name, this will take you to a full description of the function, with examples of use, tips & tricks, and common errors.


Excel Lookup and Reference Functions

Data Lookup Functions
HLOOKUP Looks up a supplied value in the first row of a table, and returns the corresponding value from another row
VLOOKUP Looks up a supplied value in the first column of a table, and returns the corresponding value from another column
LOOKUP Searches for a specific value in one data vector, and returns a value from the corresponding position of a second data vector
GETPIVOTDATA Extracts data stored in a Pivot Table
CHOOSE Returns one of a list of values, depending on the value of a supplied index number
MATCH Finds the relative position of a value in a supplied array
Row / Column / Area Information
ROW Returns the row number of a supplied range, or of the current cell
COLUMN Returns the column number of a supplied range, or of the current cell
ROWS Returns the number of rows in a supplied range
COLUMNS Returns the number of columns in a supplied range
AREAS Returns the number of areas in a supplied range
Functions To Return References to Cell Ranges
ADDRESS Returns a reference, in text format, for a supplied row and column number
INDEX Returns a reference to a cell (or range of cells) for requested rows and columns within a supplied range
INDIRECT Returns a cell or range reference that is represented by a supplied text string
OFFSET Returns a reference to a range of cells that is a specified number of rows and columns from an initial supplied range
Other
HYPERLINK Creates a hyperlink to a document in a supplied location.
TRANSPOSE Performs a transpose transformation on a range of cells (ie. transforms a horizontal range of cells into a vertical range and vice versa)
RTD Retrieves real-time data from a program that supports COM automation
FORMULATEXT Returns a formula as a string (New in Excel 2013)


See the Microsoft Office website for an excerpt, taken from John Walkenbach's excellent book Excel 2007 Formulas, which gives several examples of different uses of the Excel Lookup functions.

Return to the List of All Built-In Excel Functions

Return to the ExcelFunctions.net Home Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net