Index Match match match? #REF

[orangebloss]

I'm trying to look up values based on three criteria - my gut is that this needs to be an array formula but I'm beggared if I can get it to work. My source table looks as follows:

Project	Category	1	2	3	4	5	6
Project A	Category A	9	4	3	6	0	2
Project A	Category B	7	3	2	7	5	6
Project B	Category A	5	4	3	9	7	8
Project B	Category B	9	1	8	2	7	3

My Results table looks at the source table and returns the values based on the Project and Category specified in other cells

	A	B	C	D	E
1	Project B
2	Category	1	2	3	4
3	Category A
4	Category B

Which should produce the following results

	A	B	C	D	E
1	Project B
2	Category	1	2	3	4
3	Category A	5	4	3	9
4	Category B	9	1	8	2

Index Match Match doesn't seem to work (presumably as it's all columns?) Any assistance is much appreciated!!

Essentially if the Category, Project and month (values 1 to 4 etc) all match then return the value

Alternatively is there an alternative way to structure my source table?

 

[Aladin Akyurek]

Sheet1

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	Project	Category	1	2	3	4	5	6
2​	Project A	Category A	9	4	3	6	0	2
3​	Project A	Category B	7	3	2	7	5	6
4​	Project B	Category A	5	4	3	9	7	8
5​	Project B	Category B	9	1	8	2	7	3

Sheet2

Row\Col	A​	B​	C​	D​	E​
1​	Project B
2​	Category	1	2	3	4
3​	Category A	5	4	3	9
4​	Category B	9	1	8	2
5​

In B3 control+shift+enter, not just enter, copy across, and down:

=INDEX(Sheet1!$C$2:$H$5,MATCH(1,IF(Sheet1!$A$2:$A$5=$A$1,IF(Sheet1!$B$2:$B$5=$A3,1)),0),MATCH(B$2,Sheet1!$C$1:$H$1,0))

 

[Peter_SSs]

You could also try this variation that does not require the array entry, & for me is a little faster (only relevant if you have a lot of these formulas on your sheet).

For the same layout as Aladin posted, in B3 of Sheet2 copied across and down

=INDEX(Sheet1!$C$2:$H$5,MATCH($A$1&"|"&$A3,INDEX(Sheet1!$A$2:$A$5&"|"&Sheet1!$B$2:$B$5,0),0),MATCH(B$2,Sheet1!$C$1:$H$1,0))

 

[orangebloss]

Brilliant ! thank you to both of you! I went with the array in the end but both your swift responses are much appreciated!!

 

[Peter_SSs]

.. both your swift responses are much appreciated!!

You are very welcome.