Formula for returning multiple exact matches

[Cmoreno89]

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 the following problem. 

I'm attempting to search and find exact matches and return the cell number (only searching the A columns). Currently the formula below works but doesn't return multiple exact matches, only the first one. 

The following formula works but only returns 1 match:

=IFERROR($F5&": "&TEXTJOIN("; ", TRUE, IF(XMATCH($G$5, INDIRECT("'"&$F5&"'!A:A"), 0), "A"&XMATCH($G$5, INDIRECT("'"&$F5&"'!A:A"), 0)&" - "& $G$5&"; ", "")), $F5&": N/A")

What I'm attempting to do is have it return multiple matches, one after the other, seperated by a "; ".

$F5 = worksheet name

$G$5 = exact search phrase

And again, I'm only searching the A column for potential matches. How can we edit this formula to accommodate multiple matches? I should also mention that I'm using a mobile tablet version of excel, so we cant use vba or macros or arrays or what have you. Please help! Thank you!

[#2]

@Cmoreno89 

=F5&": "&TEXTJOIN(",",,BYROW(INDIRECT("'"&$F5&"'!A:A"),
LAMBDA(v,IF(v=$G$5,"A"&CELL("row",v),""))))&" - "&$G$5

This returns the intended result in my sheet if i correctly understand what you want to do.

[#3]

Thank you,

Just a couple tweaks needed:
1) Can you seperate each match with "; "?
2) Can a no match return: "$F5: N/A" ?

Thanks

[#4]

Is there also an alternate formula that calculates faster? It's taking ages to run through everything it seems...

And also, when I copy and pasted the formula down it down into the rest of the F column there were some "#N/A" and "#NUM!" errors. How could this be?

[#5]

Can you share some data and expected result?

[#6]

It's sensitive information, I can't share the exact details as far as data goes.

But essentially, it's a workbook with multiple worksheets.
The worksheet names are manually typed into range $F5:$F35.
The search phrases are in range $G5:$G100.
But all we're looking to do at this point is start by searching for the first search phrase, which is cell $G$5. (I will gladly manually edit each formula for the other search phrases later).
But first we need a working formula for the first search phrase, which is in cell $G$5.
And we are only searching the A columns of each worksheet. No other columns.

Thats pretty much it!

[#7]

Oh and we are looking for EXACT matches only. And if there are no matches, I'd like it to return:
"$F5: N/A"

And if there are multiple matches, I'd like it to return each match like this:
"$F5: <cell number found>; <cell number found>; <cell number found>; - <search phrase>"

Hope that makes sense. If you have any other questions please ask. Thanks!

[#8]

Searching the entirety of column A would seem to be the cause of the problem with respect to calculation time. Do you really need to parse every cell up until row 1,048,576?

[#9]

@Cmoreno89 

=F5&": "&TEXTJOIN(";",,IF(SUM(LEN(BYROW(INDIRECT("'"&$F5&"'!A:A"),
LAMBDA(v,IF(v=$G$5,"A"&CELL("row",v),"")))))=0,"NA",BYROW(INDIRECT("'"&$F5&"'!A:A"),
LAMBDA(v,IF(v=$G$5,"A"&CELL("row",v),"")))))&" - "&$G$5

You are welcome. This formula uses ; instead of , and returns NA if no exact match is found. @JosWoolley has explained why calculation might take some time, thank you.

[#10]

@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))
 )

[#11]

No actually, but every worksheet has a different amount of A rows being used. I guess we can adjust it to the largest number to save time?

[#12]

Have you ever used excel on a mobile device or tablet?

[#13]

Thank you!

Just need 1 more tweak! Can you please make it so that when there is no match it returns: "$F5: N/A" instead of "$F5: N/A - $G$5". ? Thanks!

[#14]

@Cmoreno89 

=$F5&": "&TEXTJOIN(";",,IF(SUM(LEN(BYROW(
INDIRECT("'"&$F5&"'!A1:A10000"),

LAMBDA(v,IF(v=$G$5,"A"&CELL("row",v),"")))))=0,"NA",

BYROW(INDIRECT("'"&$F5&"'!A1:A10000"),
LAMBDA(v,IF(v=$G$5,"A"&CELL("row",v),"")))))

&IF(SUM(LEN(BYROW(INDIRECT("'"&$F5&"'!A1:A10000"),

LAMBDA(v,IF(v=$G$5,"A"&CELL("row",v),"")))))=0,""," - "&$G$5)

You are welcome. This formula returns the intended output in my sheet.

[#15]

Thank you, but I'm getting "#NUM!" and "#N/A" errors when I copy and paste the formula down the column 😞

Any suggestions?

[#16]

@Cmoreno89 

Does it work if you drag the formula down? Otherwise can you attach a file without sensitive data which shows the error messages?

[#17]

The errors occured after I copy/pasted the initial formula down the same column. Does this formula search for EXACT matches? Seems like excel is confused from the formula commands? Perhaps a partial match messed up the commands?

[#18]

@Cmoreno89 

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.

Excel dynamic arrays, functions and formulas (ablebits.com)

[#19]

I'm actually on an android tablet, but if I were to enter your formula, excel wouldn't recognize it? Or am I wrong?

[#20]

How about remove sensitive information or provide dummy data?

In fact,I can not figuare out your specific problem without Excel file.
Additionally,
If formula not available,how about web service ?