Lookup and reference functions

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

See Also

• List of worksheet functions (alphabetical)
• List of worksheet functions (by category)

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.

• 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

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. 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.

5. 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.

See Also

• COLUMN function
• Create and print mailing labels for an address list in Excel
• Lookup and reference functions
• OFFSET function
• ROW function

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

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. 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.

See Also

• ADDRESS function
• CELL function
• COLUMN function
• COLUMNS function
• INDEX function
• Lookup and reference functions
• ROW function
• ROWS function

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

• If index_num is 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.), every value is evaluated when CHOOSE is evaluated.
• The value arguments to CHOOSE can be range references as well as single values.

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

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. 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.

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

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. 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.

See Also

• INDEX function
• Lookup and reference functions

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.):

• reference  Optional. The cell or range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells for which you want to return the column number.
  • If the reference argument is omitted or refers to a range of cells, and if the COLUMN function is entered as a horizontal array formula, the COLUMN function returns the column numbers of reference as a horizontal array.

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

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. 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.

5. 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.

See Also

• Change the column width and row height
• COLUMNS function
• Lookup and reference functions
• Move or copy rows and columns
• ROW function

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

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. 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.

See Also

• COLUMN function
• Count all of the cells in a range
• Lookup and reference functions
• ROWS function

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

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. 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.

See Also

• INDEX function
• Look up values in a list of data
• Lookup and reference functions
• MATCH function
• VLOOKUP function

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

• Lookup and reference functions

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.

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

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. 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.

 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

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. 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.

 Top of Page

See Also

• Look up values in a list of data
• Lookup and reference functions

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

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. 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.

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

• Lookup and reference functions
• OFFSET function

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.

 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

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. 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.

5. 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.

 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

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. 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.

5. 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.

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

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. 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.

5. 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.

 Top of Page

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• Can I turn the #N/A result from a lookup function to a zero?
• How can i use > in lookup function?
• How to create a lookup table with an added varable?
• Excel 2002: Can Vlookup search at left hand side of the table?
• Using VLOOKUP to match similar text?
• Lookup formula to return all instances of match?

See Also

• HLOOKUP function
• INDEX function
• Lookup and reference functions
• MATCH function
• VLOOKUP function

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.

 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

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. 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.

5. 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.

See Also

• Count unique values among duplicates
• HLOOKUP function
• INDEX function
• Look up values in a list of data
• Lookup and reference functions
• VLOOKUP function

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

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. 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.

See Also

• Look up values in a list of data
• Lookup and reference functions

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.

• If reference is omitted, it is assumed to be the reference of the cell in which the ROW function appears.
• If reference is a range of cells, and if ROW is entered as a vertical 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.), ROW returns the row numbers of reference as a vertical array.
• Reference cannot refer to multiple areas.

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

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. 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.

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

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. 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.

 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

• COLUMN function
• Lookup and reference functions
• ROWS function

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

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. 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.

See Also

• COLUMNS function
• Count all of the cells in a range
• Count unique values among duplicates
• Lookup and reference functions
• ROW function

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

See Also

• Lookup and reference functions

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

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. 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.

5. 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.

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

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. 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.

5. 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.

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

• LINEST function
• Lookup and reference functions
• MDETERM function
• MINVERSE function
• MMULT function
• Rearrange (transpose) data from columns to rows or vice versa

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

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. 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.

5. 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.)

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

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. 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.

5. 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.

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

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. 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.

5. 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.

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.