Index Functions

Reference functions are used to return the addresses, column numbers, and row numbers of individual cells. The number of columns or rows in a cell range can be counted. Indirect cell references can be made.

The CHOOSE Function

CHOOSE (index, item_list): Returns a value from the item_list based on the given index number.

Examples:

 

A

B

1

3

1

2

1

2

3

2

5

 

Formula

Result

=CHOOSE(2, "a", "b", "c")

b

=CHOOSE(1, 5, 6, 7, 8)

5

=CHOOSE(A3, "a", "b", "c", "d")

b

=CHOOSE(B2, A1, A2, A3)

1 (value in cell A2)

=CHOOSE(5, A1, A2, A3)

#VALUE! (index exceeds number of items in list)

=SUM(C1:CHOOSE(2, C10, C15, C20))

sum of values in cell range C1:C15

 

The INDEX Function

INDEX (reference, row, column, range_number): Returns the value in the reference with the supplied row and column number. If multiple cell references or ranges are supplied, use range_number to specify which should be used.  

Examples:

 

A

B

1

Top 3

 

2

George

150

3

Candy

135

4

Tina

125

5

Runners Up

 

6

Frank

120

7

Doug

115

8

Kate

112

9

Walter

110

 

Formula

Result

=INDEX(A2:B4, 1, 1)

George

=INDEX(A2:B4, 1, 2)

150

=INDEX(A2:B4, 3, 1)

Tina

=INDEX(A6:B9, 2, 1)

Doug

=INDEX(A6:B9, 2, 2)

115

=INDEX((A2:B4, A6:B9), 2, 1, 1)

Candy

=INDEX((A2:B4, A6:B9), 2, 1, 2)

Doug

=INDEX(A2:B4, 1, 3)

#REF! (column out of range)