INDEX [users.business.uconn.edu]



Excel Lookup Functions

INDEX

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.

Reference form

INDEX(reference,row_num,column_num,area_num)

Reference is a reference to one or more cell ranges.

❑ If you are entering a nonadjacent selection for reference, enclose reference in parentheses. For an example of using INDEX with a nonadjacent selection, see the fifth example following.

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

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.

Examples:

On the following worksheet, the range A2:C6 is named Fruit, the range A8:C11 is named Nuts, and the range A1:C11 is named Stock.

INDEX(Fruit,2,3) equals the reference C3, containing 38

INDEX((A1:C6,A8:C11),2,2,2) equals the reference B9, containing $3.55

SUM(INDEX(Stock,0,3,1)) equals SUM(C1:C11) equals 216

SUM(B2:INDEX(Fruit,5,2)) equals SUM(B2:B6) equals 2.42

MATCH

Returns the relative position of an item in an array that matches a specified value in a specified order. 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.

MATCH(lookup_value,lookup_array,match_type)

Lookup_value is the value you use to find the value you want in a table.

❑ Lookup_value is the value 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.

❑ Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array can be an array or an array reference.

Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

❑ If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

❑ If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

❑ If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.

❑ If match_type is omitted, it is assumed to be 1.

MATCH(39000,B2:B8,1) equals 3

MATCH(38000,B2:B8,0) equals 2

Examples

Note that C2:C8 contains text formatted as percent numbers.

In the preceding worksheet:

MATCH(39000,B2:B8,1) equals 3

MATCH(38000,B2:B8,0) equals 2

MATCH(39000,B2:B8,-1) equals the #N/A error value, because the range B2:B8 is ordered incorrectly for match_type -1 matching (the order must be descending to be correct).

Suppose Yen refers to A2:A8, YenDollar to A2:C8, and MyIncome to a cell containing the number ¥6,301,126.33. This formula:

"Your tax rate is "&LOOKUP(MyIncome,YenDollar)&", which places you in tax bracket number "&MATCH(MyIncome,Yen)&"."

produces this result:

"Your tax rate is 22.41%, which places you in tax bracket number 7."

OFFSET

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.

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

Reference is the reference from which you want to base the offset. Reference must be a reference 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).

If rows and cols offset reference over the edge of the worksheet, OFFSET returns the #REF! error value.

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.

If height or width is omitted, it is assumed to be the same height or width as reference.

Remarks

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.

Examples

OFFSET(C3,2,3,1,1) equals F5. If you enter this formula on a worksheet, Microsoft Excel displays the value contained in cell F5.

OFFSET(C3:E5,-1,0,3,3) equals C2:E4

OFFSET(C3:E5,0,-3,3,3) equals #REF!

INDIRECT

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.

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.

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.

Remarks

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

Examples

If cell A1 contains the text "B2", and cell B2 contains the value 1.333, then:

INDIRECT($A$1) equals 1.333

If you change the text in A1 to "C5", and cell C5 contains the value 45, then:

INDIRECT($A$1) equals 45

If the workspace is set to display R1C1-style references, cell R1C1 contains R2C2, and cell R2C2 contains the value 1.333, then:

INT(INDIRECT(R1C1,FALSE)) equals 1

If B3 contains the text "George", and a cell defined as George contains the value 10, then:

INDIRECT($B$3) equals 10

CHOOSE

Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 29 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.

CHOOSE(index_num,value1,value2,…)

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

❑ 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 29 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, 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.

Examples

CHOOSE(2,"1st","2nd","3rd","Finished") equals "2nd"

SUM(A1:CHOOSE(3,A10,A20,A30)) equals SUM(A1:A30)

If A10 contains 4, then:

CHOOSE(A10,"Nails","Screws","Nuts","Bolts") equals "Bolts"

If A10-3 equals 3, then:

CHOOSE(A10-3,"1st","2nd","3rd","Finished") equals "3rd"

If SalesOld is a name defined to refer to the value 10,000, then:

CHOOSE(2,SalesNew,SalesOld,SalesBudget) equals 10,000

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download