- Statistical Functions
- Math & Trignometric Functions
- Lookup & Reference Functions
- Advanced Excel Functions - Logical
- Information Functions
- Financial Functions
- Engineering Functions
- Date & Time Functions
- Database Functions
- Advanced Excel Functions - Cube
- Compatibility Functions
- Advanced Excel Functions - Home
Useful Resources
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
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 psts all the Lookup & Reference functions −
S.No. | Function and Description |
---|---|
1 | Returns a reference as text to a single cell in a worksheet |
2 | Returns the number of areas in a reference |
3 | Chooses a value from a pst of values |
4 | Returns the column number of a reference |
5 | Returns the number of columns in a reference |
6 | Returns the formula at the given reference as text |
7 | Returns data stored in a PivotTable |
8 | 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 | Creates a shortcut that opens a document on your hard drive, a server, or the Internet |
10 | Uses an index to choose a value from a reference or array |
11 | Returns a reference indicated by a text value |
12 | Returns a value either from a one-row or one-column range or from an array |
13 | Returns the relative position of an item in an array |
14 | Returns a reference offset from a given reference |
15 | Returns the row number of a reference |
16 | Returns the number of rows in a reference |
17 | Returns real-time data from a program that supports COM automation |
18 | Returns the transpose of an array |
19 | 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 |