Index with multiple criteria + column ranges

[ryansm05]

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Project	2019Q1	2019Q2	2019Q3	2019Q4	2020Q1	2020Q2	2020Q3	2020Q4	2021Q1	2021Q2	2021Q3	2021Q4			OPO
2	DFG	3	4	34	5	4		8	6	8			4
3	4RR		3	4	6	67	7	8	9		78		89			2019Q3
4	66L		4	5	5	878	5	9		34	4	5	34
5	OPO	9	6	4		9	7	8	98	2	66.6	35	6			2021Q2

<tbody>
</tbody>

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]

	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Project	2019Q1	2019Q2	2019Q3	2019Q4	2019Q1	2019Q2	2019Q3	2019Q4	2019Q1	2019Q2	2019Q3	2019Q4			OPO
2	DFG	3	4	34	5	4		8	6	8			4
3	4RR		3	4	6	67	7	8	9		78		89			2019Q3
4	66L		4	5	5	878	5	9		34	4	5	34
5	OPO	9	6	4		9	7	8	98	2	66.6	35	6			2019Q2

<tbody>
</tbody>

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?