Forum Discussion

Cmoreno89's avatar
Cmoreno89
Brass Contributor
Oct 07, 2023

Formula for returning multiple exact matches

  Hi all,   I've been working on a formula for several days now but haven't been able to fully figure it out.  ChatGPT helped me get to this point but it can't figure out how to completely solve ...
excel
Excel on mobile
Formulas and Functions
office 365
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 08, 2023

Cmoreno89 

"so we can't use VBA or macros or arrays or what have you"

I am not convinced that statement is correct; my understanding is that Dynamic arrays, Lambda, Typescript, Python were all introduced as part of the push to achieve platform independence.

As a 365 beta user I haven't used anything but array formulas for several years now.

 

I would suggest using a 3D array to hold the relevant portion of column A across sheets and then something of the sort

= LET(
    nameheader, TOROW(sheetName),
    located,    HSTACK(data)=target,
    rowIndex,   SEQUENCE(ROWS(located)),
    rowNumber,  BYCOL(IF(located, rowIndex, ""), LAMBDA(c, TEXTJOIN(";", , c))),
    FILTER(nameheader & ": " & rowNumber & " - " & target, LEN(rowNumber))
 )

 

Cmoreno89's avatar
Cmoreno89
Brass Contributor
Oct 08, 2023
Have you ever used excel on a mobile device or tablet?
    • Cmoreno89's avatar
      Cmoreno89
      Brass Contributor
      Oct 08, 2023
      I'm actually on an android tablet, but if I were to enter your formula, excel wouldn't recognize it? Or am I wrong?
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Oct 08, 2023

        Cmoreno89 

        I believe it should work but I have no way of testing that.

        I suggest building the formula step by step to test the functionality. 

        First one needs 3D named ranges 'data' and 'sheetname' (direct references could be used but look horrible).

        Then you test to see whether TOROW and HSTACK exist.  If they do, you are on a roll.  If not, you are stuck with legacy methods 😞

Resources