Intersection of column and row based on value match

[MrSamExcel]

My file has two sheets: 'Formula' and 'Class1'. Is there a formula – non-array, not VBA – that I can place in ‘Formula’ sheet -> cell B2 that will return from ‘Class1’ sheet the YTD Return for Month Ending date 12/31/2019? In this example, the result of the formula would be 6.66%.

You can see my current INDEX-MATCH-MATCH formula in B2 works but requires me to manually specify the row containing Month Ending values – Row 5 -- but would be better if formula could find row that begins with text “Month Ending” from ‘Class1’ sheet and reference the column from the same row that contains value 12/31/2019. The value being looked up in Column A -- "YTD Return" in this example -- will always be in Column A. I’ve also tried with FILTER and LOOKUP functions but I’m missing something. Thanks.

Intersection of column and row based on text matches.xlsx
	A	B	C
1		12/31/2019	12/31/2020
2	YTD Return	6.66%	7.00%
Formula

Intersection of column and row based on text matches.xlsx
	A	B	C	D	E	F	G	H	I
1	Class1
2	Date
3
4	Quarter Ending		12/31/2018	12/31/2018	12/31/2019	12/31/2019	12/31/2020	9/30/2023	9/30/2023
5	Month Ending		11/30/2018	12/31/2018	5/31/2019	12/31/2019	12/31/2020	7/31/2023	8/31/2023
6	Static				3.50%	3.50%	3.50%	3.50%	3.50%
7	Monthly Return				5.00%	6.00%	7.00%	8.00%	9.99%
8
9
10	YTD				0.64%	0.74%	1.26%	1.51%	1.68%
11	YTD Return					6.66%	7.00%	8.00%	8.88%
12
13	1 Year Return						7.00%	8.00%	1.11%
Class1

 

[kevin9999]

Book1
	A	B	C
1		12/31/2019	12/31/2020
2	YTD Return	6.66%	7.00%
Formula

Cell Formulas
Range	Formula
B2:C2	B2	=INDEX(Class1!$C$11:$HK$11,MATCH(B1,Class1!$C$5:$HK$5,0))

 

[MrSamExcel]

B2=INDEX(Class1!$C$11:$HK$11,MATCH(B1,Class1!$C$5:$HK$5,0))

Thanks for your response, but this formula appears to have similar limitations as my original in that it requires the user to manually specify rows -- in this case 11 and 5. Is there a different approach that can look up rows/cells using text match for "YTD Return" with Month Ending date match 12/31/2019?

 

[Fluff]

How about

=INDEX(Class1!$A$1:$HK$5000,MATCH($A2,Class1!$A1:$A5000,0),MATCH(B$1,INDEX(Class1!$A$1:$HK$5000,MATCH("Month Ending",Class1!$A$1:$A$5000,0),0),0))

 

[MrSamExcel]

How about

=INDEX(Class1!$A$1:$HK$5000,MATCH($A2,Class1!$A1:$A5000,0),MATCH(B$1,INDEX(Class1!$A$1:$HK$5000,MATCH("Month Ending",Class1!$A$1:$A$5000,0),0),0))

YES, that works. Thanks Fluff!

 

[Fluff]

Glad we could help & thanks for the feedback.