1

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.

1 Answer 1

1

Try:

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.