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:


[table="width: 500"]
[tr]
[td]Project[/td]
[td]Category[/td]
[td]1[/td]
[td]2[/td]
[td]3[/td]
[td]4[/td]
[td]5[/td]
[td]6[/td]

[/tr]
[tr]
[td]Project A[/td]
[td]Category A[/td]
[td]9[/td]
[td]4[/td]
[td]3[/td]
[td]6[/td]
[td]0[/td]
[td]2[/td]

[/tr]
[tr]
[td]Project A[/td]
[td]Category B[/td]
[td]7[/td]
[td]3[/td]
[td]2[/td]
[td]7[/td]
[td]5[/td]
[td]6[/td]

[/tr]
[tr]
[td]Project B[/td]
[td]Category A[/td]
[td]5[/td]
[td]4[/td]
[td]3[/td]
[td]9[/td]
[td]7[/td]
[td]8[/td]

[/tr]
[tr]
[td]Project B[/td]
[td]Category B[/td]
[td]9[/td]
[td]1[/td]
[td]8[/td]
[td]2[/td]
[td]7[/td]
[td]3[/td]

[/tr]
[/table]

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

[table="width: 500"]
[tr]
[td][/td]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[td]E[/td]
[/tr]
[tr]
[td]1[/td]
[td]Project B[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]2[/td]
[td]Category[/td]
[td]1[/td]
[td]2[/td]
[td]3[/td]
[td]4[/td]
[/tr]
[tr]
[td]3[/td]
[td]Category A[/td]

[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]4[/td]
[td]Category B[/td]

[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[/table]

Which should produce the following results

[table="width: 500"]
[tr]
[td][/td]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[td]E[/td]
[/tr]
[tr]
[td]1[/td]
[td]Project B[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]2[/td]
[td]Category[/td]
[td]1[/td]
[td]2[/td]
[td]3[/td]
[td]4[/td]
[/tr]
[tr]
[td]3[/td]
[td]Category A[/td]

[td]5[/td]
[td]4[/td]
[td]3[/td]
[td]9[/td]
[/tr]
[tr]
[td]4[/td]
[td]Category B[/td]

[td]9[/td]
[td]1[/td]
[td]8[/td]
[td]2[/td]
[/tr]
[/table]

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

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr][tr][td]

1​

[/td][td] Project[/td][td] Category[/td][td] 1[/td][td] 2[/td][td] 3[/td][td] 4[/td][td] 5[/td][td] 6[/td][/tr]
[tr][td]

2​

[/td][td] Project A[/td][td] Category A[/td][td] 9[/td][td] 4[/td][td] 3[/td][td] 6[/td][td] 0[/td][td] 2[/td][/tr]
[tr][td]

3​

[/td][td] Project A[/td][td] Category B[/td][td] 7[/td][td] 3[/td][td] 2[/td][td] 7[/td][td] 5[/td][td] 6[/td][/tr]
[tr][td]

4​

[/td][td] Project B[/td][td] Category A[/td][td] 5[/td][td] 4[/td][td] 3[/td][td] 9[/td][td] 7[/td][td] 8[/td][/tr]
[tr][td]

5​

[/td][td] Project B[/td][td] Category B[/td][td] 9[/td][td] 1[/td][td] 8[/td][td] 2[/td][td] 7[/td][td] 3[/td][/tr]
[/table]

Sheet2

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td] Project B[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

2​

[/td][td] Category[/td][td] 1[/td][td] 2[/td][td] 3[/td][td] 4[/td][/tr]
[tr][td]

3​

[/td][td] Category A[/td][td] 5[/td][td] 4[/td][td] 3[/td][td] 9[/td][/tr]
[tr][td]

4​

[/td][td] Category B[/td][td] 9[/td][td] 1[/td][td] 8[/td][td] 2[/td][/tr]
[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.