Use Of INDEX
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)
|
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.
Syntax 1
=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.
Colours
|
Red
|
Green
|
Blue
|
Size
|
Large
|
Medium
|
Small
|
Type either 1, 2 or 3 :
|
2
| |
The size is :
|
Medium
| |
=INDEX(G34:I34,H36)
|
Type either 1, 2 or 3 :
|
2
| |
The colour is :
|
Green
| |
=INDEX(D32:D34,D36)
|
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)
|
Example
This is an extended version of the previous example.
It allows the names of products and the quarters to be entered.
The =MATCH() function is used to find the row and column positions of the names entered.
These positions are then used by the =INDEX() function to look for the data.
EAST
|
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
|
WEST
|
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 :
|
wood
| |||
Type 1, 2, 3 or 4 for the Qtr :
|
qtr2
| |||
Type 1 for North or 2 for South :
|
west
|
The result is :
|
6500
|
=INDEX(EastAndWest,MATCH(F100,C91:C93,0),MATCH(F101,D90:G90,0),IF(F102=C90,1,IF(F102=C95,2)))
कोई टिप्पणी नहीं:
एक टिप्पणी भेजें