LikeOffice.com

excel utility

Keep In Touch:
 contact us  facebook
 
Get HELP with your Excel Project:
 Excel Formula
 List of Excel Formula examples
 
Home >> excel 2007 >> Excel 2007 - Lookup and reference functions

This page presents how to work with Microsoft Excel 2007 features and its redesigned interface. You may also visit our Excel Question page.

 

   Lookup and reference functions

   ADDRESS function

   AREAS function

   CHOOSE function

   COLUMN function

   COLUMNS function

   HLOOKUP function

   HYPERLINK function

   INDEX function

   INDIRECT function

   LOOKUP function

   MATCH function

   OFFSET function

   ROW function

   ROWS function

   RTD function

   TRANSPOSE function

   VLOOKUP function

 

 

 

 

Lookup and reference functions

Click one of the links in the following list to see detailed help about the function.

Function

Description

ADDRESS

Returns a reference as text to a single cell in a worksheet

AREAS

Returns the number of areas in a reference

CHOOSE

Chooses a value from a list of values

COLUMN

Returns the column number of a reference

COLUMNS

Returns the number of columns in a reference

HLOOKUP

Looks in the top row of an array and returns the value of the indicated cell

HYPERLINK

Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet

INDEX

Uses an index to choose a value from a reference or array

INDIRECT

Returns a reference indicated by a text value

LOOKUP

Looks up values in a vector or array

MATCH

Looks up values in a reference or array

OFFSET

Returns a reference offset from a given reference

ROW

Returns the row number of a reference

ROWS

Returns the number of rows in a reference

RTD

Retrieves real-time data from a program that supports COM automation (Automation: A way to work with an application's objects from another application or development tool. Formerly called OLE Automation, Automation is an industry standard and a feature of the Component Object Model (COM).)

TRANSPOSE

Returns the transpose of an array

VLOOKUP

Looks in the first column of an array and moves across the row to return the value of a cell

 

See Also

 

 

Excel > Function reference > Lookup and reference

ADDRESS function

Excel 2007

This article describes the formula syntax and usage of the ADDRESS function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Find links to information about working with mailing addresses or creating mailing labels in the See Also section.

Description

You can use the ADDRESS function to obtain the address of a cell in a worksheet, given specified row and column numbers. For example, ADDRESS(2,3) returns $C$2. As another example, ADDRESS(77,300) returns $KN$77. You can use other functions, such as the ROW and COLUMN functions, to provide the row and column number arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.) for the ADDRESS function.

Syntax

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

The ADDRESS function syntax has the following arguments:

  • row_num  Required. A numeric value that specifies the row number to use in the cell reference.
  • column_num  Required. A numeric value that specifies the column number to use in the cell reference.
  • abs_num  Optional. A numeric value that specifies the type of reference to return.

abs_num

Returns this type of reference

1 or omitted

Absolute (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.)

2

Absolute row; relative column

3

Relative row; absolute column

4

Relative

  • A1  Optional. A logical value that specifies the A1 or R1C1 reference style. In A1 style, columns are labeled alphabetically, and rows are labeled numerically. In R1C1 reference style, both columns and rows are labeled numerically. If the A1 argument is TRUE or omitted, the ADDRESS function returns an A1-style reference; if FALSE, the ADDRESS function returns an R1C1-style reference.

 Note    To change the reference style that Excel uses, click the Microsoft Office Button , click Excel Options, and then click Formulas. Under Working with formulas, select or clear the R1C1 reference style check box.

  • sheet_text  Optional. A text value that specifies the name of the worksheet to be used as the external reference. For example, the formula =ADDRESS(1,1,,,"Sheet2") returns Sheet2!$A$1. If the sheet_text argument is omitted, no sheet name is used, and the address returned by the function refers to a cell on the current sheet.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1


2


3




4





5




6

A

B

C

Formula

Description

Result

=ADDRESS(2,3)

Absolute reference

$C$2

=ADDRESS(2,3,2)

Absolute row; relative column

C$2

=ADDRESS(2,3,2,FALSE)

Absolute row; relative column in R1C1 reference style

R2C[3]

=ADDRESS(2,3,1,FALSE,"[Book1]Sheet1")

Absolute reference to another workbook and worksheet

[Book1]Sheet1!R2C3

=ADDRESS(2,3,1,FALSE,"EXCEL SHEET")

Absolute reference to another worksheet

'EXCEL SHEET'!R2C3

 

See Also

 

 

Excel > Function reference > Lookup and reference

AREAS function

Excel 2007

Returns the number of areas in a reference. An area is a range of contiguous cells or a single cell.

Syntax

AREAS(reference)

Reference   is a reference to a cell or range of cells and can refer to multiple areas. If you want to specify several references as a single argument, then you must include extra sets of parentheses so that Microsoft Excel will not interpret the comma as a field separator. See the following example.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

4

A

B

Formula

Description (Result)

=AREAS(B2:D4)

Number of areas in the range (1)

=AREAS((B2:D4,E5,F6:I9))

Number of areas in the range (3)

=AREAS(B2:D4 B2)

Number of areas in the range (1)

 

See Also

 

 

Excel > Function reference > Lookup and reference

CHOOSE function

Excel 2007

Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index number. For example, if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index_num.

Syntax

CHOOSE(index_num,value1,value2,...)

Index_num   specifies which value argument is selected. Index_num must be a number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254.

  • If index_num is 1, CHOOSE returns value1; if it is 2, CHOOSE returns value2; and so on.
  • If index_num is less than 1 or greater than the number of the last value in the list, CHOOSE returns the #VALUE! error value.
  • If index_num is a fraction, it is truncated to the lowest integer before being used.

Value1,value2,...   are 1 to 254 value arguments from which CHOOSE selects a value or an action to perform based on index_num. The arguments can be numbers, cell references, defined names, formulas, functions, or text.

Remarks

For example, the formula:

=SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10))

evaluates to:

=SUM(B1:B10)

which then returns a value based on the values in the range B1:B10.

The CHOOSE function is evaluated first, returning the reference B1:B10. The SUM function is then evaluated using B1:B10, the result of the CHOOSE function, as its argument.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

4

5

A

B

Data

Data

1st

Nails

2nd

Screws

3rd

Nuts

Finished

Bolts

Formula

Description (Result)

=CHOOSE(2,A2,A3,A4,A5)

Value from the second argument A3 (2nd)

=CHOOSE(4,B2,B3,B4,B5)

Value from the fourth argument B5 (Bolts)

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

4

5

A

 

Data

23

45

12

10

Formula

Description (Result)

=SUM(A2:CHOOSE(2,A3,A4,A5))

Sums the range A2:A4 (80)

 

See Also

 

 

Excel > Function reference > Lookup and reference

COLUMN function

Excel 2007

This article describes the formula syntax and usage of the COLUMN function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel. Find links to more information about formatting columns in the See Also section.

Description

Returns the column number of the given cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.). For example, the formula =COLUMN(D10) returns 4, because column D is the fourth column.

Syntax

COLUMN(reference)

The COLUMN function syntax has the following argument (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

To enter a formula as an array formula  Starting with the formula cell, select the range that you want to contain the array formula. Press F2, and then press CTRL+SHIFT+ENTER.

    • If the reference argument is a range of cells, and if the COLUMN function is not entered as a horizontal array formula, the COLUMN function returns the number of the leftmost column.
    • If the reference argument is omitted, it is assumed to be the reference of the cell in which the COLUMN function appears.
    • The reference argument cannot refer to multiple areas.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1





2



3

A

B

C

Formula

Description

Result

=COLUMN()

Column in which the formula appears

Because no argument is specified, the function returns the value of the column that contains the formula. In this case, the formula is in a cell in column A, so the function returns 1.

=COLUMN(C10)

Column number of the reference C10

Because column C is the third column, the function returns 3.

 

See Also

 

 

Excel > Function reference > Lookup and reference

COLUMNS function

Excel 2007

Returns the number of columns in an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) or reference.

Syntax

COLUMNS(array)

Array   is an array or array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.), or a reference to a range of cells for which you want the number of columns.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

A

B

Formula

Description (Result)

=COLUMNS(C1:E4)

Number of columns in the reference (3)

=COLUMNS({1,2,3;4,5,6})

Number of columns in the array constant (3)

 

See Also

 

 

Excel > Function reference > Lookup and reference

HLOOKUP function

Excel 2007

Searches for a value in the top row of a table or an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.

The H in HLOOKUP stands for "Horizontal."

Syntax

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

Lookup_value   is the value to be found in the first row of the table. Lookup_value can be a value, a reference, or a text string.

Table_array   is a table of information in which data is looked up. Use a reference to a range or a range name.

  • The values in the first row of table_array can be text, numbers, or logical values.
  • If range_lookup is TRUE, the values in the first row of table_array must be placed in ascending order: ...-2, -1, 0, 1, 2,... , A-Z, FALSE, TRUE; otherwise, HLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
  • Uppercase and lowercase text are equivalent.
  • Sort the values in ascending order, left to right. For more information, see Sort data.

Row_index_num   is the row number in table_array from which the matching value will be returned. A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2 returns the second row value in table_array, and so on. If row_index_num is less than 1, HLOOKUP returns the #VALUE! error value; if row_index_num is greater than the number of rows on table_array, HLOOKUP returns the #REF! error value.

Range_lookup   is a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Remarks

  • If HLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.
  • If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value.
  • If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

4

A

B

C

Axles

Bearings

Bolts

4

4

9

5

7

10

6

8

11

Formula

Description (Result)

 

=HLOOKUP("Axles",A1:C4,2,TRUE)

Looks up Axles in row 1, and returns the value from row 2 that's in the same column. (4)

 

=HLOOKUP("Bearings",A1:C4,3,FALSE)

Looks up Bearings in row 1, and returns the value from row 3 that's in the same column. (7)

 

=HLOOKUP("B",A1:C4,3,TRUE)

Looks up B in row 1, and returns the value from row 3 that's in the same column. Because B is not an exact match, the next largest value that is less than B is used: Axles. (5)

 

=HLOOKUP("Bolts",A1:C4,4)

Looks up Bolts in row 1, and returns the value from row 4 that's in the same column. (11)

 

=HLOOKUP(3,{1,2,3;"a","b","c";"d","e","f"},2,TRUE)

Looks up 3 in the first row of the array constant, and returns the value from row 2 in same column. (c)

 

 

See Also

 

 

Excel > Function reference > Lookup and reference

HYPERLINK function

Excel 2007

Creates a shortcut or jump that opens a document stored on a network server, an intranet (intranet: A network within an organization that uses Internet technologies (such as the HTTP or FTP protocol). By using hyperlinks, you can explore objects, documents, pages, and other destinations on the intranet.), or the Internet. When you click the cell that contains the HYPERLINK function, Microsoft Excel opens the file stored at link_location.

Syntax

HYPERLINK(link_location,friendly_name)

Link_location   is the path and file name to the document to be opened as text. Link_location can refer to a place in a document such as a specific cell or named range in an Excel worksheet or workbook, or to a bookmark in a Microsoft Word document. The path can be to a file stored on a hard disk drive, or the path can be a universal naming convention (UNC) path on a server (in Microsoft Excel for Windows) or a Uniform Resource Locator (URL (Uniform Resource Locator (URL): An address that specifies a protocol (such as HTTP or FTP) and a location of an object, document, World Wide Web page, or other destination on the Internet or an intranet, for example: http://www.microsoft.com/.)) path on the Internet or an intranet.

  • Link_location can be a text string enclosed in quotation marks or a cell that contains the link as a text string.
  • If the jump specified in link_location does not exist or cannot be navigated, an error appears when you click the cell.

Friendly_name   is the jump text or numeric value that is displayed in the cell. Friendly_name is displayed in blue and is underlined. If friendly_name is omitted, the cell displays the link_location as the jump text.

  • Friendly_name can be a value, a text string, a name, or a cell that contains the jump text or value.
  • If friendly_name returns an error value (for example, #VALUE!), the cell displays the error instead of the jump text.

Remark

To select a cell that has a hyperlink in it without jumping to the hyperlink destination, click the cell and hold the mouse button until the cursor becomes a cross , then release the mouse button.

Examples

The following example opens a worksheet named Budget Report.xls that is stored on the Internet at the location named

example.microsoft.com/report

and displays the text "Click for report":

=HYPERLINK("http://example.microsoft.com/report/budget report.xls", "Click for report")

The following example creates a hyperlink to cell F10 on the worksheet named Annual in the workbook Budget Report.xls, which is stored on the Internet at the location named

example.microsoft.com/report

. The cell on the worksheet that contains the hyperlink displays the contents of cell D1 as the jump text:

=HYPERLINK("[http://example.microsoft.com/report/budget report.xls]Annual!F10", D1)

The following example creates a hyperlink to the range named DeptTotal on the worksheet named First Quarter in the workbook Budget Report.xls, which is stored on the Internet at the location named

example.microsoft.com/report

. The cell on the worksheet that contains the hyperlink displays the text "Click to see First Quarter Department Total":

=HYPERLINK("[http://example.microsoft.com/report/budget report.xls]First Quarter!DeptTotal", "Click to see First Quarter Department Total")

To create a hyperlink to a specific location in a Microsoft Word document, you must use a bookmark to define the location you want to jump to in the document. The following example creates a hyperlink to the bookmark named QrtlyProfits in the document named Annual Report.doc located at

example.microsoft.com

:

=HYPERLINK("[http://example.microsoft.com/Annual Report.doc]QrtlyProfits", "Quarterly Profit Report")

In Excel for Windows, the following example displays the contents of cell D5 as the jump text in the cell and opens the file named 1stqtr.xls, which is stored on the server named FINANCE in the Statements share. This example uses a UNC path:

=HYPERLINK("\\FINANCE\Statements\1stqtr.xls", D5)

The following example opens the file 1stqtr.xls in Excel for Windows that is stored in a directory named Finance on drive D, and displays the numeric value stored in cell H10:

=HYPERLINK("D:\FINANCE\1stqtr.xls", H10)

In Excel for Windows, the following example creates a hyperlink to the area named Totals in another (external) workbook, Mybook.xls:

=HYPERLINK("[C:\My Documents\Mybook.xls]Totals")

In Microsoft Excel for the Macintosh, the following example displays "Click here" in the cell and opens the file named First Quarter that is stored in a folder named Budget Reports on the hard drive named Macintosh HD:

=HYPERLINK("Macintosh HD:Budget Reports:First Quarter", "Click here")

You can create hyperlinks within a worksheet to jump from one cell to another cell. For example, if the active worksheet is the sheet named June in the workbook named Budget, the following formula creates a hyperlink to cell E56. The link text itself is the value in cell E56.

=HYPERLINK("[Budget]June!E56", E56)

To jump to a different sheet in the same workbook, change the name of the sheet in the link. In the previous example, to create a link to cell E56 on the September sheet, change the word "June" to "September."

 

See Also

 

 

Excel > Function reference > Lookup and reference

INDEX function

Excel 2007

Returns a value or the reference to a value from within a table or range. There are two forms of the INDEX function: the array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) form and the reference form.

If you want to

Then see

Return the value of a specified cell or array of cells

Array form

Return a reference to specified cells

Reference form

Array form

Returns the value of an element in a table or an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.), selected by the row and column number indexes.

Use the array form if the first argument to INDEX is an array constant.

INDEX(array,row_num,column_num)

Array   is a range of cells or an array constant.

  • If array contains only one row or column, the corresponding row_num or column_num argument is optional.
  • If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.

Row_num   selects the row in array from which to return a value. If row_num is omitted, column_num is required.

Column_num   selects the column in array from which to return a value. If column_num is omitted, row_num is required.

Remarks

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

A

B

Data

Data

Apples

Lemons

Bananas

Pears

Formula

Description (Result)

=INDEX(A2:B3,2,2)

Value at the intersection of the second row and second column in the range (Pears)

=INDEX(A2:B3,2,1)

Value at the intersection of the second row and first column in the range (Bananas)

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

A

B

Formula

Description (Result)

=INDEX({1,2;3,4},0,2)

Value in the first row, second column in the array constant (2)

 

Value in the second row, second column in the array constant (4)

 Note    The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range A2:A3 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 2.

 Top of Page

Reference form

Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of nonadjacent selections, you can pick the selection to look in.

INDEX(reference,row_num,column_num,area_num)

Reference   is a reference to one or more cell ranges.

  • If you are entering a nonadjacent range for the reference, enclose reference in parentheses.
  • If each area in reference contains only one row or column, the row_num or column_num argument, respectively, is optional. For example, for a single row reference, use INDEX(reference,,column_num).

Row_num   is the number of the row in reference from which to return a reference.

Column_num   is the number of the column in reference from which to return a reference.

Area_num   selects a range in reference from which to return the intersection of row_num and column_num. The first area selected or entered is numbered 1, the second is 2, and so on. If area_num is omitted, INDEX uses area 1.

  • For example, if reference describes the cells (A1:B4,D1:E4,G1:H4), then area_num 1 is the range A1:B4, area_num 2 is the range D1:E4, and area_num 3 is the range G1:H4.

Remarks

  • After reference and area_num have selected a particular range, row_num and column_num select a particular cell: row_num 1 is the first row in the range, column_num 1 is the first column, and so on. The reference returned by INDEX is the intersection of row_num and column_num.
  • If you set row_num or column_num to 0 (zero), INDEX returns the reference for the entire column or row, respectively.
  • Row_num, column_num, and area_num must point to a cell within reference; otherwise, INDEX returns the #REF! error value. If row_num and column_num are omitted, INDEX returns the area in reference specified by area_num.
  • The result of the INDEX function is a reference and is interpreted as such by other formulas. Depending on the formula, the return value of INDEX may be used as a reference or as a value. For example, the formula CELL("width",INDEX(A1:B2,1,2)) is equivalent to CELL("width",B1). The CELL function uses the return value of INDEX as a cell reference. On the other hand, a formula such as 2*INDEX(A1:B2,1,2) translates the return value of INDEX into the number in cell B1.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

4

5

6

7

8

9

10

11

A

B

C

Fruit

Price

Count

Apples

0.69

40

Bananas

0.34

38

Lemons

0.55

15

Oranges

0.25

25

Pears

0.59

40

 

 

 

Almonds

2.80

10

Cashews

3.55

16

Peanuts

1.25

20

Walnuts

1.75

12

Formula

Description (Result)

 

=INDEX(A2:C6,2,3)

The intersection of the second row and third column in the range A2:C6, which is the content of cell C3. (38)

 

=INDEX((A1:C6,A8:C11),2,2,2)

The intersection of the second row and second column in the second area of A8:C11, which is the content of cell B9. (3.55)

 

=SUM(INDEX(A1:C11,0,3,1))

The sum of the third column in the first area of the range A1:C11, which is the sum of C1:C6. (216)

 

=SUM(B2:INDEX(A2:C6,5,2))

The sum of the range starting at B2, and ending at the intersection of the fifth row and the second column of the range A2:A6, which is the sum of B2:B6. (2.42)

 

 Top of Page

 

See Also

 

 

Excel > Function reference > Lookup and reference

INDIRECT function

Excel 2007

Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Syntax

INDIRECT(ref_text,a1)

Ref_text   is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! error value.

  • If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.
  • If ref_text refers to a cell range outside the row limit of 1,048,576 or the column limit of 16,384 (XFD), INDIRECT returns a #REF! error.

 Note    This behavior is different from Excel versions earlier than Microsoft Office Excel 2007, which ignore the exceeded limit and return a value.

A1   is a logical value that specifies what type of reference is contained in the cell ref_text.

  • If a1 is TRUE or omitted, ref_text is interpreted as an A1-style reference.
  • If a1 is FALSE, ref_text is interpreted as an R1C1-style reference.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

4

5

A

B

Data

Data

B2

1.333

B3

45

George

10

5

62

Formula

Description (Result)

=INDIRECT($A$2)

Value of the reference in cell A2 (1.333)

=INDIRECT($A$3)

Value of the reference in cell A3 (45)

=INDIRECT($A$4)

If the cell B4 has the defined name "George," the value of the defined name is returned (10)

=INDIRECT("B"&$A$5)

Value of a reference in cell A5 (62)

When you create a formula that refers to a cell, the reference to the cell will be updated if: (1) the cell is moved by using the Cut command to delete the cell or (2) the cell is moved because rows or columns are inserted or deleted. If you always want the formula to refer to the same cell regardless of whether the row above the cell is deleted or the cell is moved, use the INDIRECT worksheet function. For example, if you always want to refer to cell A10, use the following syntax:

=INDIRECT("A10")

 

See Also

 

 

Excel > Function reference > Lookup and reference

LOOKUP function

Excel 2007

This article describes the formula syntax and usage of the LOOKUP function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

The LOOKUP function returns a value either from a one-row or one-column range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) or from an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.). The LOOKUP function has two syntax forms: the vector form and the array form.

If you want to

Then see

Usage

Look in a one-row or one-column range (known as a vector) for a value and return a value from the same position in a second one-row or one-column range

Vector form

Use the vector form when you have a large list of values to look up or when the values may change over time.

Look in the first row or column of an array for the specified value and return a value from the same position in the last row or column of the array

Array form

Use the array form when you have a small list of values and the values remain constant over time.

 Notes 

  • You can also use the LOOKUP function as an alternative to the IF function for elaborate tests or tests that exceed the limit for nesting of functions. See the examples in the array form.
  • For the LOOKUP function to work correctly, the data being looked up must be sorted in ascending order. If this is not possible, consider using the VLOOKUP, HLOOKUP, or MATCH functions.

Vector form

A vector is a range of only one row or one column. The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range. Use this form of the LOOKUP function when you want to specify the range that contains the values that you want to match. The other form of LOOKUP automatically looks in the first column or row.

Syntax

LOOKUP(lookup_value, lookup_vector, result_vector)

The LOOKUP function vector form syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

  • lookup_value  Required. A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.
  • lookup_vector  Required. A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values.

Important  The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

  • result_vector  Required. A range that contains only one row or column. The result_vector argument must be the same size as lookup_vector.

Remarks

  • If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.
  • If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

4

5

6

7



8




9




10




11

A

B

C

Frequency

Color

 

4.14

red

 

4.19

orange

 

5.17

yellow

 

5.77

green

 

6.39

blue

 

Formula

Description

Result

=LOOKUP(4.19,A2:A6,B2:B6)

Looks up 4.19 in column A, and returns the value from column B that is in the same row.

orange

=LOOKUP(5.00,A2:A6,B2:B6)

Looks up 5.00 in column A, matches the next smallest value (4.19), and returns the value from column B that is in the same row.

orange

=LOOKUP(7.66,A2:A6,B2:B6)

Looks up 7.66 in column A, matches the next smallest value (6.39), and returns the value from column B that is in the same row.

blue

=LOOKUP(0,A2:A6,B2:B6)

Looks up 0 in column A, and returns an error because 0 is less than the smallest value in the lookup_vector A2:A7.

#N/A

 Top of Page

Array form

The array form of LOOKUP looks in the first row or column of an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) for the specified value and returns a value from the same position in the last row or column of the array. Use this form of LOOKUP when the values that you want to match are in the first row or column of the array. Use the other form of LOOKUP when you want to specify the location of the column or row.

Tip  In general, it's best to use the HLOOKUP or VLOOKUP function instead of the array form of LOOKUP. This form of LOOKUP is provided for compatibility with other spreadsheet programs.

Syntax

LOOKUP(lookup_value, array)

The LOOKUP function array form syntax has these arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

  • lookup_value  Required. A value that LOOKUP searches for in an array. The lookup_value argument can be a number, text, a logical value, or a name or reference that refers to a value.
    • If LOOKUP can't find the value of lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.
    • If the value of lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.
  • array  Required. A range of cells that contains text, numbers, or logical values that you want to compare with lookup_value.

The array form of LOOKUP is very similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for the value of lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array.

    • If array covers an area that is wider than it is tall (more columns than rows), LOOKUP searches for the value of lookup_value in the first row.
    • If an array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column.
    • With the HLOOKUP and VLOOKUP functions, you can index down or across, but LOOKUP always selects the last value in the row or column.

Important  The values in array must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1





2





3

A

B

C

Formula

Description

Result

=LOOKUP("C",{"a","b","c","d";1,2,3,4})

Looks up "C" in the first row of the array, finds the largest value that is less than or equal to it ("c"), and then returns the value in the last row that is in the same column.

3

=LOOKUP("bump",{"a",1;"b",2;"c",3})

Looks up "bump" in the first row of the array, finds the largest value that is less than or equal to it ("b"), and then returns the value in the last column that is in the same row.

2

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

The following example uses an array of numbers to assign a letter grade to a test score.

 

1

2

3

4

5













6













7













8













9












10












11

A

B

C

Score

 

 

45

 

 

90

 

 

78

 

 

Formula

Description

Result

=LOOKUP(A2,{0,60,70,80,90},{"F","D","C","B","A"})

Looks up the value in A2 (45) in the first row of the array, finds the largest value that is less than or equal to it (0), and then returns the value in the last row of the array that is in the same column.

F

=LOOKUP(A3,{0,60,70,80,90},{"F","D","C","B","A"})

Looks up the value in A3 (90) in the first row of the array, finds the largest value that is less than or equal to it (90), and then returns the value in the last row of the array that is in the same column.

A

=LOOKUP(A4,{0,60,70,80,90},{"F","D","C","B","A"})

Looks up the value in A4 (78) in the first row of the array, finds the largest value that is less than or equal to it (70), and then returns the value in the last row of the array that is in the same column.

C

=LOOKUP(A2,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})

Looks up the value in A2 (45) in the first row of the array, finds the largest value that is less than or equal to it (0), and then returns the value in the last row of the array that is in the same column.

F

=LOOKUP(A3,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})

Looks up the value in A3 (90) in the first row of the array, finds the largest value that is less than or equal to it (90), and then returns the value in the last row that is in the same column.

A-

=LOOKUP(A4,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"})

Looks up the value in A4 (78) in the first row of the array, finds the largest value that is less than or equal to it (77), and then returns the value in the last row that is in the same column.

C+

 Top of Page

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

 

See Also

 

 

Excel > Function reference > Lookup and reference

MATCH function

Excel 2007

This article describes the formula syntax and usage of the MATCH function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

The MATCH function searches for a specified item in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula

=MATCH(25,A1:A3,0)

returns the number 2, because 25 is the second item in the range.

Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. For example, you might use the MATCH function to provide a value for the row_num argument of the INDEX function.

Syntax

MATCH(lookup_value, lookup_array, [match_type])

The MATCH function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

  • lookup_value  Required. The value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.

The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

  • lookup_array  Required. The range of cells being searched.
  • match_type  Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

The following table describes how the function finds values based on the setting of the match_type argument.

Match_type

Behavior

1 or omitted

MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

0

MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.

-1

MATCH finds the smallest value that is greater than or equal to lookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

 Notes 

  • MATCH returns the position of the matched value within lookup_array, not the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, which is the relative position of "b" within the array {"a","b","c"}.
  • MATCH does not distinguish between uppercase and lowercase letters when matching text values.
  • If MATCH is unsuccessful in finding a match, it returns the #N/A error value.
  • If match_type is 0 and lookup_value is a text string, you can use the wildcard characters  the question mark (?) and asterisk (* in the lookup_value argument. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

4

5

6



7


8


9

A

B

C

Product

Count

 

Bananas

25

 

Oranges

38

 

Apples

40

 

Pears

41

 

Formula

Description

Result

=MATCH(39,B2:B5,1)

Because there is not an exact match, the position of the next lowest value (38) in the range B2:B5 is returned.

2

=MATCH(41,B2:B5,0)

The position of the value 41 in the range B2:B5.

4

=MATCH(40,B2:B5,-1)

Returns an error because the values in the range B2:B5 are not in descending order.

#N/A

 

See Also

 

 

Excel > Function reference > Lookup and reference

OFFSET function

Excel 2007

Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.

Syntax

OFFSET(reference,rows,cols,height,width)

Reference   is the reference from which you want to base the offset. Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.

Rows   is the number of rows, up or down, that you want the upper-left cell to refer to. Using 5 as the rows argument specifies that the upper-left cell in the reference is five rows below reference. Rows can be positive (which means below the starting reference) or negative (which means above the starting reference).

Cols   is the number of columns, to the left or right, that you want the upper-left cell of the result to refer to. Using 5 as the cols argument specifies that the upper-left cell in the reference is five columns to the right of reference. Cols can be positive (which means to the right of the starting reference) or negative (which means to the left of the starting reference).

Height   is the height, in number of rows, that you want the returned reference to be. Height must be a positive number.

Width   is the width, in number of columns, that you want the returned reference to be. Width must be a positive number.

Remarks

  • If rows and cols offset reference over the edge of the worksheet, OFFSET returns the #REF! error value.
  • If height or width is omitted, it is assumed to be the same height or width as reference.
  • OFFSET doesn't actually move any cells or change the selection; it just returns a reference. OFFSET can be used with any function expecting a reference argument. For example, the formula SUM(OFFSET(C2,1,2,3,1)) calculates the total value of a 3-row by 1-column range that is 1 row below and 2 columns to the right of cell C2.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

4

A

B

Formula

Description (Result)

=OFFSET(C3,2,3,1,1)

Displays the value in cell F5 (0)

=SUM(OFFSET(C3:E5,-1,0,3,3))

Sums the range C2:E4 (0)

=OFFSET(C3:E5,0,-3,3,3)

Returns an error, because the reference is not on the worksheet (#REF!)

 

See Also

 

 

Excel > Function reference > Lookup and reference

ROW function

Excel 2007

Returns the row number of a reference.

Syntax

ROW(reference)

Reference   is the cell or range of cells for which you want the row number.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

A

B

Formula

Description (Result)

=ROW()

Row in which the formula appears (2)

=ROW(C10)

Row of the reference (10)

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

4

A

B

Formula

Description (Result)

=ROW(C4:D6)

First row in the reference (4)

 

Second row in the reference (5)

 

Third row in the reference (6)

 Note    The formula in the example must be entered as an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.). After copying the example to a blank worksheet, select the range A2:A4 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 4.

 

See Also

 

 

Excel > Function reference > Lookup and reference

ROWS function

Excel 2007

Returns the number of rows in a reference or array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.).

Syntax

ROWS(array)

Array   is an array, an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.), or a reference to a range of cells for which you want the number of rows.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

A

B

Formula

Description (Result)

=ROWS(C1:E4)

Number of rows in the reference (4)

=ROWS({1,2,3;4,5,6})

Number of rows in the array constant (2)

 

See Also

 

 

Excel > Function reference > Lookup and reference

RTD function

Excel 2007

Retrieves real-time data from a program that supports COM automation (COM add-in: A supplemental program that extends the capabilities of a Microsoft Office program by adding custom commands and specialized features. COM add-ins can run in one or more Office programs. COM add-ins use the file name extension .dll or .exe.).

Syntax

=RTD(ProgID,server,topic1,[topic2],...)

ProgID   the name of the ProgID of a registered COM automation add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) that has been installed on the local computer. Enclose the name in quotation marks.

server   name of the server where the add-in should be run. If there is no server, and the program is run locally, leave the argument blank. Otherwise, enter quotation marks ("") around the server name. When using RTD within Visual Basic for Applications (VBA) (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Microsoft Windows-based applications and is included with several Microsoft programs.), double quotation marks or the VBA NullString property are required for the server, even if the server is running locally.

topic1, topic2,...   1 to 253 parameters that together represent a unique piece of real-time data.

Remarks

  • The RTD COM automation add-in must be created and registered on a local computer. If you haven't installed a real-time data server, you will get an error message in a cell when you try to use the RTD function.
  • When the server has been programmed to continually update results, unlike other functions, RTD formulas will change when Microsoft Excel is in automatic calculation mode.

Example

Formula

Description (Result)

=RTD("MyComAddIn.Progid",,"LOREM_IPSUM","Price")

Data

 

See Also

 

 

Excel > Function reference > Lookup and reference

TRANSPOSE function

Excel 2007

This article describes the formula syntax and usage of the TRANSPOSE function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Find a link to more information about transposing data in the See Also section.

Description

The TRANSPOSE function returns a vertical range of cells as a horizontal range, or vice versa. The TRANSPOSE function must be entered as an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) in a range that has the same number of rows and columns, respectively, as the source range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array or range on a worksheet.

Syntax

TRANSPOSE(array)

The TRANSPOSE function syntax has the following argument (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

  • array  Required. An array or range of cells on a worksheet that you want to transpose. The transpose of an array is created by using the first row of the array as the first column of the new array, the second row of the array as the second column of the new array, and so on.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

4

5

6

A

B

C

Data

Data

Data

1

2

3

Formula

Description

Result

=TRANSPOSE($A$2:$C$2)

Value from first column

1

 

Value from second column

2

 

Value from third column

3

Important  The formula shown in the example must be entered as an array formula for the TRANSPOSE function to work as expected. After copying the example to a blank worksheet, select the range A4:A6, starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 1.

Example 2

Some functions, such as LINEST, return horizontal arrays. The LINEST function returns a horizontal array of the slope and Y-intercept for a line. The following formula returns a vertical array of the slope and Y-intercept by using the LINEST function.

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

 

1

2

3

4

5

6

7

8

A

B

C

Known y

Known x

 

1

0

 

9

4

 

5

2

 

7

3

 

Formula

Description

Result

=TRANSPOSE(LINEST(A2:A5,B2:B5,,FALSE))

Slope

2

 

Y-intercept

1

Important  The formula shown in the example must be entered as an array formula for the TRANSPOSE function to work as expected. After copying the example to a blank worksheet, select the range A7:A8, starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single result is 2.

 

See Also

 

 

Excel > Function reference > Lookup and reference

VLOOKUP function

Excel 2007

Tags  commands; find; formula; functions; lookup; reference; search; troubleshoot;   vlookup

What are tags?

This article describes the formula syntax and usage of the VLOOKUP function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

You can use the VLOOKUP function to search the first column of a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells, and then return a value from any cell on the same row of the range. For example, suppose that you have a list of employees contained in the range A2:C10. The employees' ID numbers are stored in the first column of the range, as shown in the following illustration.

If you know the employee's ID number, you can use the VLOOKUP function to return either the department or the name of that employee. To obtain the name of employee number 38, you can use the formula =VLOOKUP(38, A2:C10, 3, FALSE). This formula searches for the value 38 in the first column of the range A2:C10, and then returns the value that is contained in the third column of the range and on the same row as the lookup value ("Axel Delgado").

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

Syntax

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The VLOOKUP function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

  • lookup_value  Required. The value to search in the first column of the table or range. The lookup_value argument can be a value or a reference. If the value you supply for the lookup_value argument is smaller than the smallest value in the first column of the table_array argument, VLOOKUP returns the #N/A error value.
  • table_array  Required. The range of cells that contains the data. You can use a reference to a range (for example, A2:D8), or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.
  • col_index_num  Required. The column number in the table_array argument from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on.

If the col_index_num argument is:

    • Less than 1, VLOOKUP returns the #VALUE! error value.
    • Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.
  • range_lookup  Optional. A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:
    • If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

Important  If range_lookup is either TRUE or is omitted, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

For more information, see Sort data.

If range_lookup is FALSE, the values in the first column of table_array do not need to be sorted.

    • If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

This example searches the Density column of an atmospheric properties table to find corresponding values in the Viscosity and Temperature columns. (The values are for air at 0 degrees Celsius at sea level, or 1 atmosphere.)

 

1

2

3

4

5

6

7

8

9

10

11







12







13





14





15







16

A

B

C

Density

Viscosity

Temperature

0.457

3.55

500

0.525

3.25

400

0.606

2.93

300

0.675

2.75

250

0.746

2.57

200

0.835

2.38

150

0.946

2.17

100

1.09

1.95

50

1.29

1.71

0

Formula

Description

Result

=VLOOKUP(1,A2:C10,2)

Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A which is 0.946, and then returns the value from column B in the same row.

2.17

=VLOOKUP(1,A2:C10,3,TRUE)

Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A, which is 0.946, and then returns the value from column C in the same row.

100

=VLOOKUP(0.7,A2:C10,3,FALSE)

Using an exact match, searches for the value 0.7 in column A. Because there is no exact match in column A, an error is returned.

#N/A

=VLOOKUP(0.1,A2:C10,2,TRUE)

Using an approximate match, searches for the value 0.1 in column A. Because 0.1 is less than the smallest value in column A, an error is returned.

#N/A

=VLOOKUP(2,A2:C10,2,TRUE)

Using an approximate match, searches for the value 2 in column A, finds the largest value less than or equal to 2 in column A, which is 1.29, and then returns the value from column B in the same row.

1.71

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

This example searches the Item-ID column of a baby products table and matches values in the Cost and Markup columns to calculate prices and test conditions.

 

1

2

3

4

5

6

7




8





9






10







11

A

B

C

D

Item-ID

Item

Cost

Markup

ST-340

Stroller

$145.67

30%

BI-567

Bib

$3.56

40%

DI-328

Diapers

$21.45

35%

WI-989

Wipes

$5.12

40%

AS-469

Aspirator

$2.56

45%

Formula

Description

 

Result

= VLOOKUP("DI-328", A2:D6, 3, FALSE) * (1 + VLOOKUP("DI-328", A2:D6, 4, FALSE))

Calculates the retail price of diapers by adding the markup percentage to the cost.

$28.96

= (VLOOKUP("WI-989", A2:D6, 3, FALSE) * (1 + VLOOKUP("WI-989", A2:D6, 4, FALSE))) * (1 - 20%)

Calculates the sale price of wipes by subtracting a specified discount from the retail price.

$5.73

= IF(VLOOKUP(A2, A2:D6, 3, FALSE) >= 20, "Markup is " & 100 * VLOOKUP(A2, A2:D6, 4, FALSE) &"%", "Cost is under $20.00")

If the cost of an item is greater than or equal to $20.00, displays the string "Markup is nn%"; otherwise, displays the string "Cost is under $20.00".

Markup is 30%

= IF(VLOOKUP(A3, A2:D6, 3, FALSE) >= 20, "Markup is: " & 100 * VLOOKUP(A3, A2:D6, 4, FALSE) &"%", "Cost is $" & VLOOKUP(A3, A2:D6, 3, FALSE))

If the cost of an item is greater than or equal to $20.00, displays the string Markup is nn%"; otherwise, displays the string "Cost is $n.nn".

Cost is $3.56

Example 3

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

  1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

This example searches the ID column of an employee table and matches values in other columns to calculate ages and test for error conditions.

 


1


2


3


4


5


6


7

8







9









10









11






12

A

B

C

D

E

ID

Last name

First name

Title

Birth date

1

Davis

Sara

Sales Rep.

12/8/1968

2

Fontana

Olivier

V.P. of Sales

2/19/1952

3

Leal

Karina

Sales Rep.

8/30/1963

4

Patten

Michael

Sales Rep.

9/19/1958

5

Burke

Brian

Sales Mgr.

3/4/1955

6

Sousa

Luis

Sales Rep.

7/2/1963

Formula

Description

Result

=INT(YEARFRAC(DATE(2004,6,30), VLOOKUP(5,A2:E7,5, FALSE), 1))

For the fiscal year 2004, finds the age of the employee with ID equal to 5. Uses the YEARFRAC function to subtract the birth date from the fiscal year end date and displays the result as an integer using the INT function.

49

=IF(ISNA(VLOOKUP(5,A2:E7,2,FALSE)) = TRUE, "Employee not found", VLOOKUP(5,A2:E7,2,FALSE))

If there is an employee with an ID of 5, displays the employee's last name; otherwise, displays the message "Employee not found".

The ISNA function returns a TRUE value when the VLOOKUP function returns the #NA error value.

Burke

=IF(ISNA(VLOOKUP(15,A3:E8,2,FALSE)) = TRUE, "Employee not found", VLOOKUP(15,A3:E8,2,FALSE))

If there is an employee with an ID of 15, displays the employee's last name; otherwise, displays the message "Employee not found".

The ISNA function returns a TRUE value when the VLOOKUP function returns the #NA error value.

Employee not found

=VLOOKUP(4,A2:E7,3,FALSE) & " " & VLOOKUP(4,A2:E7,2,FALSE) & " is a " & VLOOKUP(4,A2:E7,4,FALSE)

For the employee with an ID of 4, concatenates the values of three cells into a complete sentence.

Michael Patten is a Sales Rep.

Remarks

  • When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not contain leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP might return an incorrect or unexpected value.

For more information, see CLEAN function and TRIM function.

  • When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, VLOOKUP might return an incorrect or unexpected value.

For more information, see Convert numbers stored as text to numbers.

  • If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters  the question mark (?) and asterisk (*)  in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

 

                  

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
©2003-2013 LikeOffice - Privacy Policy - Contact us