I would like to set an ARRAYFORMULA or something similar in C3 which can read a sheet whith name changing dynamically whith INDIRECT , so I can get the postions of each values with XMATCH :
I first tried to do this in D3:
XMATCH(A3:A;INDIRECT(B3:B&"!$1:$1"))
But 2 problems with this :
1: Length of Column A and B can change dynamically, so C has to be dynamic.
2: Not clean
In C3 I set the following formula :
ARRAYFORMULA(XMATCH(A3:A;INDIRECT(B3:B&"!$1:$1")))
and also tried :
ARRAYFORMULA(XMATCH(A3:A;query(INDIRECT(B3:B&"!$1:$1");"select *")))
You can see this works only for the first sheet name "WHISKEY.US" and it seems pretty logical.
My bet is we have to use a MAP , but number of sheets may change so we cannot initialize things.
>Solution :
Can you give a shot at:
=map(A3:A;B3:B;lambda(a;b;if(len(a)*len(b);xmatch(a;indirect(b&"!1:1"));)))