Index with multiple criteria + column ranges

[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]2020Q1
[/TD]
[TD]2020Q2
[/TD]
[TD]2020Q3
[/TD]
[TD]2020Q4
[/TD]
[TD]2021Q1
[/TD]
[TD]2021Q2
[/TD]
[TD]2021Q3
[/TD]
[TD]2021Q4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]OPO
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]DFG
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]34
[/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]8
[/TD]
[TD]6
[/TD]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]4RR
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]6
[/TD]
[TD]67
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD]78
[/TD]
[TD][/TD]
[TD]89
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2019Q3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]66L
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]878
[/TD]
[TD]5
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD]34
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]34
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]OPO
[/TD]
[TD]9
[/TD]
[TD]6
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]9
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]98
[/TD]
[TD]2
[/TD]
[TD]66.6
[/TD]
[TD]35
[/TD]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2021Q2
[/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 = 4 (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 = 66.6 (cell L5)



I'm sue there is a funky Sumifs/Index or Sumproduct … I'm just struggling with my attempts.

Any help will me much appreciated!

Thanks
Ryan

 

[Gerald Higgins]

Take a look at this thread from a few hours ago which is basically asking the same question
https://www.mrexcel.com/forum/excel-questions/1107330-index-match-query.html

 

[Fluff]

How about
=INDEX($C$2:$N$5,MATCH($Q$1,$B$2:$B$5,0),MATCH($Q3,$C$1:$N$1,0))

Then just change the $Q3 to $Q5

 

[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]OPO
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]DFG
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]34
[/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]8
[/TD]
[TD]6
[/TD]
[TD]8
[/TD]
[TD][/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]4RR
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]6
[/TD]
[TD]67
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD]78
[/TD]
[TD][/TD]
[TD]89
[/TD]
[TD][/TD]
[TD][/TD]
[TD]2019Q3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]66L
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]878
[/TD]
[TD]5
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD]34
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]34
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]OPO
[/TD]
[TD]9
[/TD]
[TD]6
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD]9
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]98
[/TD]
[TD]2
[/TD]
[TD]66.6
[/TD]
[TD]35
[/TD]
[TD]6
[/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 = 4 (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 = 66.6 (cell L5)

Apologies - my original post was obviously easily solved by an Index-Match.

However what I was meaning to illustrate is that there is multiple dates e.g 2019Q1 on the same line (3 instances of this). Therefore, I need the formula to look at a specified range of columns which are not necessarily the columns immediately after the lookup column.

Hope that makes sense. Again, any help is hugely appreciated.

 

[Fluff]

Simplest way
=INDEX($C$2:$F$5,MATCH($Q$1,$B$2:$B$5,0),MATCH($Q3,$C$1:$F$1,0))
=INDEX($K$2:$N$5,MATCH($Q$1,$B$2:$B$5,0),MATCH($Q5,$K$1:$N$1,0))

Other than that, how would the formula know which group of cells to look at for the column?