INDEX Function (Functions in Excel)

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			Type either 1, 2 or 3 :	2
The colour is :	Green			The size is :	Medium
	=INDEX(D32:D34,D36)				=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)

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

Related Posts by Categories