Multiple Column Index and Match

[hshone]

Hi all,

I'm trying to get a multiple column index and match. The data set is much larger but looking to find who has had sales in what region.

I have the below data: (Stating in Cells A7)

Region/ Sales	Scotland	England	Wales	Ireland
Dave	2	3	7	1
Jess	2	4	5	2

What I'm trying to work out is how many sales Dave has had in Scotland such as the below: (Starting in Cells A13)

	Scotland
Dave	2

The current formula I'm using is below but keeps returning #N/A (I have entered it as an array formula) the answer is obviously 2 but cant get it to work.

=INDEX(B8:E11,MATCH(1,($A14=A8:A11)*($B$13=B7:E7),0))

Any help is much appreciated.

Thanks :D

 

[FryGirl]

try =INDEX(B8:E9,MATCH(A13,A8:A9,0),MATCH(B12,B7:E7,0))

 

[dave3009]

Hi

Give this a go

Excel 2007
	A	B	C	D	E
7	Region/ Sales	Scotland	England	Wales	Ireland
8	Dave	2	3	7	1
9	Jess	2	4	5	2
10
11
12
13		England
14	Dave	3
Sheet5

Cell Formulas
Range	Formula
B14	=INDEX(B8:E11,MATCH(A14,A8:A11, 0),MATCH($B$13, B7:E7,0))

HTH

Dave