Lookup and Reference Functions

Lookup & Reference functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets. They perform tasks such as providing information about a range, returning the location of a given address or value, or looking up specific values.

Lookup and Reference Functions

The following table lists all the Lookup & Reference functions −

S.No.	Function and Description
1	ADDRESS Returns a reference as text to a single cell in a worksheet
2	AREAS Returns the number of areas in a reference
3	CHOOSE Chooses a value from a list of values
4	COLUMN Returns the column number of a reference
5	COLUMNS Returns the number of columns in a reference
6	FORMULATEXT Returns the formula at the given reference as text
7	GETPIVOTDATA Returns data stored in a PivotTable
8	HLOOKUP Searches for a value in the top row of a table and then returns a value in the same column from a row you specify in the table
9	HYPERLINK Creates a shortcut that opens a document on your hard drive, a server, or the Internet
10	INDEX Uses an index to choose a value from a reference or array
11	INDIRECT Returns a reference indicated by a text value
12	LOOKUP Returns a value either from a one-row or one-column range or from an array
13	MATCH Returns the relative position of an item in an array
14	OFFSET Returns a reference offset from a given reference
15	ROW Returns the row number of a reference
16	ROWS Returns the number of rows in a reference
17	RTD Returns real-time data from a program that supports COM automation
18	TRANSPOSE Returns the transpose of an array
19	VLOOKUP Searches for a value in the leftmost column of a table and then returns a value in the same row from a column you specify in the table