How to select a Sheet whithin an ARRAYFORMULA while sheet name change dynamically with INDIRECT


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:


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 :


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:


Leave a ReplyCancel reply