Index/Sumif or Sumproduct

[ryansm05]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[TD]P
[/TD]
[TD]Q
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]PROJECT
[/TD]
[TD]2019Q1
[/TD]
[TD]2019Q2
[/TD]
[TD]2019Q3
[/TD]
[TD]2019Q4
[/TD]
[TD]2019Q1
[/TD]
[TD]2019Q2
[/TD]
[TD]2019Q3
[/TD]
[TD]2019Q4
[/TD]
[TD]2019Q1
[/TD]
[TD]2019Q2
[/TD]
[TD]2019Q3
[/TD]
[TD]2019Q4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]OP
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]YU
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]45
[/TD]
[TD]77
[/TD]
[TD]7
[/TD]
[TD]7
[/TD]
[TD]5
[/TD]
[TD]98
[/TD]
[TD]8
[/TD]
[TD]6
[/TD]
[TD]99
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]HU
[/TD]
[TD]12
[/TD]
[TD]34
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]1
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]9
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2019Q3

[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]PO
[/TD]
[TD]15
[/TD]
[TD]65
[/TD]
[TD]45
[/TD]
[TD]18
[/TD]
[TD]25
[/TD]
[TD]45
[/TD]
[TD]66
[/TD]
[TD]5
[/TD]
[TD]8
[/TD]
[TD]8
[/TD]
[TD]6
[/TD]
[TD]9

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]OP
[/TD]
[TD]78
[/TD]
[TD]9
[/TD]
[TD]51

[/TD]
[TD]5
[/TD]
[TD]7
[/TD]
[TD]56
[/TD]
[TD]90
[/TD]
[TD]89
[/TD]
[TD][/TD]
[TD]8
[/TD]
[TD]64
[/TD]
[TD]431
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2019Q2

[/TD]
[/TR]
</tbody>[/TABLE]

Hi,

From the above data I'm hoping to achieve the below:

Formula 1:
- Looks along C1:F1 and matches the data in cell Q3
- Looks down B2:B5 and matches the data in cell Q1
- Answer = 51 (cell e5)

Formula2:
- Looks along K1:N1 and matches the data in cell Q5
- Looks down B2:B5 and matches the data in cell Q1
- Answer = 8 (cell L5)
with

I had posted something similar in recent days but confused it with an incorrect table. Ultimately, Formula2 is what I'm struggling with as the traditional Index-Match will not work because there are multiple 2019Q2 for example on row 1.

Any help is much appreciated.

Thanks
Ryan

 

[steve the fish]

Re: Index/Sumif or Sumproduct help

Something like:

=INDEX($C$2:$F$5,MATCH($Q$1,$B$2:$B$5,0),MATCH($Q$3,$C$1:$F$1,0))

or

=SUMIFS(INDEX($C$2:$F$5,,MATCH($Q$3,$C$1:$F$1,0)),$B$2:$B$5,$Q$1)

 

[Fluff]

Re: Index/Sumif or Sumproduct help

Duplicate https://www.mrexcel.com/forum/excel...ia-column-ranges-post5328310.html#post5328310

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).