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 :

Here the issue

What’s inside a sheet

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"));)))