Formula for returning multiple exact matches

Brass Contributor

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 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!

24 Replies

Re: Formula for returning multiple exact matches

=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.

Re: Formula for returning multiple exact matches

Thank you,

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

Thanks

Re: Formula for returning multiple exact matches

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?

Re: Formula for returning multiple exact matches

Can you share some data and expected result?

Re: Formula for returning multiple exact matches

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!

Re: Formula for returning multiple exact matches

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!

Re: Formula for returning multiple exact matches

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?

Re: Formula for returning multiple exact matches

=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.

Re: Formula for returning multiple exact matches

"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(
located,    HSTACK(data)=target,
rowIndex,   SEQUENCE(ROWS(located)),
rowNumber,  BYCOL(IF(located, rowIndex, ""), LAMBDA(c, TEXTJOIN(";", , c))),
FILTER(nameheader & ": " & rowNumber & " - " & target, LEN(rowNumber))
)``````

Re: Formula for returning multiple exact matches

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?

Re: Formula for returning multiple exact matches

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

Re: Formula for returning multiple exact matches

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!

Re: Formula for returning multiple exact matches

=\$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.

Re: Formula for returning multiple exact matches

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

Any suggestions?

Re: Formula for returning multiple exact matches

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

Re: Formula for returning multiple exact matches

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?

Re: Formula for returning multiple exact matches

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

Re: Formula for returning multiple exact matches

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

Re: Formula for returning multiple exact matches

How about remove sensitive information or provide dummy data?

In fact,I can not figuare out your specific problem without Excel file.