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