Advanced Excel Functions 简明教程

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	ADDRESSReturns a reference as text to a single cell in a worksheet
2	AREASReturns the number of areas in a reference
3	CHOOSEChooses a value from a list of values
4	COLUMNReturns the column number of a reference
5	COLUMNSReturns the number of columns in a reference
6	FORMULATEXTReturns the formula at the given reference as text
7	GETPIVOTDATAReturns data stored in a PivotTable
8	HLOOKUPSearches 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	HYPERLINKCreates a shortcut that opens a document on your hard drive, a server, or the Internet
10	INDEXUses an index to choose a value from a reference or array
11	INDIRECTReturns a reference indicated by a text value
12	LOOKUPReturns a value either from a one-row or one-column range or from an array
13	MATCHReturns the relative position of an item in an array
14	OFFSETReturns a reference offset from a given reference
15	ROWReturns the row number of a reference
16	ROWSReturns the number of rows in a reference
17	RTDReturns real-time data from a program that supports COM automation
18	TRANSPOSEReturns the transpose of an array
19	VLOOKUPSearches 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