Forum Discussion
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 ...
"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))
)
- Have you ever used excel on a mobile device or tablet?
No, I stick to Windows desktop, so I just hoped that your application might be one of:
- Excel 365 for Windows
- Excel 365 for Mac
- Excel 2021
- Excel 2021 for Mac
- Excel for iPad
- Excel for iPhone
- Excel for Android tablets
- Excel for Android phones
- Excel for the web
which support Dynamic arrays.
- I'm actually on an android tablet, but if I were to enter your formula, excel wouldn't recognize it? Or am I wrong?
