Create formula from cell value

[henryg]

I have a cell with the formula

=XLOOKUP(A7,'Dec20'!A7:A21,'Dec20'!D7:D21)

which I want to change so that the Dec20 part of the formula is derived from a month referenced in another cell eg K1 with 31/01/21

I can get 'Jan21'! via ="'"&TEXT(K1,"mmmyy")&"'!" but I can't manage to successfully concatenate that into the formula to replace 'Dec20'!

 

[Fluff]

How about

=XLOOKUP(A7,indirect("'"&TEXT(K1,"mmmyy")&"'!A7:A21"),indirect("'"&TEXT(K1,"mmmyy")&"'!D7:D21"))

 

[etaf]

Edit.,
Post while I was composing

perhaps an indirect()
=XLOOKUP(A7,INDIRECT("'"&TEXT(K1,"MMMYY")&"'!A7:A21"),INDIRECT("'"&TEXT(K1,"MMMYY")&"'!D7:D21"))

 

[henryg]

Indirect! Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.