VLOOKUP Text with INDIRECT

[jski21]

Good Day Excelites,

Trying to get this formula to work across 17 tabs: =VLOOKUP("Yr 47",INDIRECT("'"&P$1&"'!"&"S1:T9"),18,0).

Excerpt of the range in each spreadsheet is here, along with the dynamic cell (Q1) that is picking up the numbered spreadsheet name:

NDA WARD REPORT - 2021-22 - Yrs 40-47.xlsm
	P	Q	R	S	T
1	12	#N/A		Yr	12
2				Yr 40	0.00
3				Yr 41	0.00
4				Yr 42	0.00
5				Yr 43	0.00
6				Yr 44	0.00
7				Yr 45	56,876.11
8				Yr 46	435,294.00
9				Yr 47	435,294.00
Charts

Cell Formulas
Range	Formula
P1	P1	=RIGHT(O1,2)
Q1	Q1	=VLOOKUP("Yr 47",INDIRECT("'"&P$1&"'!"&"S1:T9"),18,0)

Trying to get Cell Q1 to pull in Cell T9. Current result is #N/A. BTW, "Yr 47" will change from year-to-year as time goes on. I don't use INDIRECT that often, but am finding more uses for it as I explore ways to improve reporting. Any assistance would be most appreciated.

Thanks all and enjoy your day.


jski

 

[Fluff]

As you only have two columns that you are looking up, the 18 should be 2

 

[jski21]

Thought it was the # of columns from the left in each sheet, but now see it is range dependent. You've been very helpful in the past Fluff and as always, thanks!

 

[Fluff]

Glad to help & thanks for the feedback.