Formula to find specific text in a column then return last value in same row

[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 find the first occurrence of text “Monthly Return” in ‘Class1’ sheet -> column A and return the last / right-most populated value in the same row? In my example, the result of the formula would be 9.99%.

You can see my current LOOKUP formula in B2 on the ‘Formula’ sheet only works if I manually specify the row for each time period. Ex) For the 1 mo period I specified $A7:$Z7, but would be better if formula could find row that begins with “Monthly Return” from ‘Class1’ sheet. Thanks.

Find text and return value in last cell of same row.xlsx
	A	B	C	D
1		1 mo	YTD	1 yr
2	Class1	9.99%	8.88%	1.11%
Formula

Find text and return value in last cell of same row.xlsx
	A	B	C	D	E	F	G	H	I
1	Class1
2	Date
3
4	Quarter Ending		12/31/2018	12/31/2018	6/30/2019	6/30/2019	6/30/2023	9/30/2023	9/30/2023
5	Month Ending		11/30/18	12/31/18	5/31/19	6/30/19	6/30/23	7/31/23	8/31/23
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.00%	7.00%	8.00%	8.88%
12
13	1 Year Return						7.00%	8.00%	1.11%
Class1

 

[DanteAmor]

How about:

=LET(a,FILTER(Class1!A1:Z20,Class1!A1:A20="Monthly Return"),LOOKUP(2,1/(a<>""),a))

 

[MrSamExcel]

How about:

=LET(a,FILTER(Class1!A1:Z20,Class1!A1:A20="Monthly Return"),LOOKUP(2,1/(a<>""),a))

This is great, thank you!
I haven't used the LET function before, reading about it now. My understanding is that FILTER returns only the row from Class1 tab that begins with my specified text. Then LET names that row "a". Then I can use the original LOOKUP to return the last value in the row we've named "a".

 

[MrSamExcel]

Add'l thoughts as I learn about LET, in case this helps future readers: The LET function in this formula is optional, it simply serves to make the formula shorter/tidier.
FILTER is the critical element that convert my original LOOKUP from manual row number assignment to text-match row assignment. So the following would also work:

=LOOKUP(2,1/(FILTER(Class1!$A$1:$Z$20,Class1!$A$1:$A$20="Monthly Return")<>""),FILTER(Class1!$A$1:$Z$20,Class1!$A$1:$A$20="Monthly Return"))

For my use case, I still prefer the LET approach proposed by DanteAmor, as I think it will be easier to maintain for the team I will hand this over.