INDEX

Posted on 18-01-2020 , by: admin , in , 0 Comments

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)

Leave a comment