index

What Does It Do ?
This function picks a value from a range of data by looking down a specified number of rows and then across a specified number of columns. It can be used with a single block of data, or non-continuos blocks.

Syntax
There are various forms of syntax for this function.

=INDEX(RangeToLookIn,Coordinate)
This is used when the RangeToLookIn is either a single column or row. The Co-ordinate indicates how far down or across to look when picking the data from the range. Both of the examples below use the same syntax, but the Co-ordinate refers to a row when the range is vertical and a column when the range is horizontal.

Holiday booking price list

 People Weeks 1 2 3 4 1 £500 £300 £250 £200 2 £600 £400 £300 £250 3 £700 £500 £350 £300
 How many weeks required : 2 How many people in the party : 4
 Cost per person is : 250 =INDEX(D7:G9,G11,G12)
 Colours Red Green Blue

 Type either 1, 2 or 3 : 2 The colour is : Green =INDEX(D32:D34,D36)
 Size Large Medium Small

 Type either 1, 2 or 3 : 2 The size is : Medium =INDEX(G34:I34,H36)

Syntax 2
=INDEX(RangeToLookIn, RowCoordinate, ColumnColumnCordinate)
This syntax is used when the range is made up of rows and columns.

 Country Currency Population Capitol England Sterling 50 M London France Franc 40 M Paris Germany DM 60 M Bonn Spain Peseta 30 M Barcelona
 Type 1,2,3 or 4 for the country : 2 Type 1,2 or 3 for statistics : 3
 The result is : Paris =INDEX(D45:F48,F50,F51)

Syntax 3
=INDEX (NamedRangeToLookIn, RowCoordinate, ColumnColumnCordinate, AreaToPickFrom) Using this syntax the range to look in can be made up of multiple areas. The easiest way to refer to these areas is to select them and give them a single name.

The AreaToPickFrom indicates which of the multiple areas should be used.

In the following example the figures for North and South have been named as one range called NorthAndSouth.

 NORTH Qtr1 Qtr2 Qtr3 Qtr4 Bricks £1,000 £2,000 £3,000 £4,000 Wood £5,000 £6,000 £7,000 £8,000 Glass £9,000 £10,000 £11,000 £12,000
 SOUTH Qtr1 Qtr2 Qtr3 Qtr4 Bricks £1,500 £2,500 £3,500 £4,500 Wood £5,500 £6,500 £7,500 £8,500 Glass £9,500 £10,500 £11,500 £12,500
 Type 1, 2 or 3 for the product : 1 Type 1, 2, 3 or 4 for the Qtr : 3 Type 1 for North or 2 for South : 2

 The result is : 3500 =INDEX(NorthAndSouth, F76,F77,F78)