How do I convert this excel formula to work in Google Sheets?

Question

In excel I have this: =INDEX(PayoutID,MATCH(TRUE,COUNTIFS(INDIRECT("'"&PayoutID&"'!$D:$D"),$A3)>0,0)) in B3

It looks at cell A3, then using a named range PayoutID (a list of sheet names), it finds the number in A3 and reports the sheet name. All good.

When I import in to google sheets it converts it to this formula: =ARRAY_CONSTRAIN(ARRAYFORMULA(INDEX(PayoutID,MATCH(TRUE,COUNTIFS(INDIRECT("'"&PayoutID&"'!$D:$D"),$A6)>0,0))), 1, 1)

This only works if the match is made on the first sheet name listed in the named range. Once it's done that one it shows #N/A for the rest.

Can someone amend my Google Sheet formula so it will work across all sheet names in the named range regardless of if there is 5 or 50??

I've spent a lot of hours searching and trying different things and cannot get it working.

Ultimately it is not finding a match, but that is only because it's not looking in all sheets listed in the named range.

Answer 1

Try:

=ARRAYFORMULA(XLOOKUP(1,N(BYROW(PayoutID,LAMBDA(ζ,COUNTIF(INDIRECT("'"&ζ&"'!D:D"),A3)))>0),PayoutID))