Lookup and Reference Functions


Advertisements


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



Advertisements
E-Books Store