INDEX & MATCH function

INDEX formula is used to extract the value from array by giving row & column references

Syntax –

=INDEX(array,row position,column position)

Example –we have provided you the one Region / Month table(array) where we are going to apply this function

7	B	C	D	E	F
8	Region	Jan	Feb	Mar	Apr
9	East	2	3	4	5
10	West	6	2	4	3
11	North	11	2	2	1
12	South	3	1	1	3

Now here we are going to takeout data for Jan month & North Region

The references for this combination in the table would be 1^st column & 3^rd Row

Then formula would be -

Result	Formula
11	=INDEX($C$9:$F$12,3,1)

The Row & column position can be find out through MATCH function also just by giving selection criteria

Syntax –

=MATCH(lookup_value,lookup_array,match_type)

Selection Table for criteria–

9	B	C	D	E
10	Field	Criteria	Result	Formula
11	Region	North	3	=MATCH($C$27,$B$9:$B$12,0)
12	Month	Jan	1	=MATCH($C$28,$C$8:$F$8,0)

Match function will return the Region(North) position as 3 & month (Jan) position as 1

Now we are going to use Index with match function for same result

Result	Formula
11	=INDEX($C$9:$F$12,MATCH($C$28,$B$9:$B$12,0),MATCH($C$29,$C$8:$F$8,0))