Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Copilot for Microsoft 365 Tech Accelerator

Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)

Microsoft Tech Community

- Home
- Microsoft 365
- Excel
- Re: Formula for returning multiple exact matches

- Subscribe to RSS Feed
- Mark Discussion as New
- Mark Discussion as Read
- Pin this Discussion for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 07 2023 02:22 PM - edited Oct 07 2023 09:03 PM

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!

Labels:

24 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 07 2023 03:11 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 07 2023 03:17 PM

Thank you,

Just a couple tweaks needed:

1) Can you seperate each match with "; "?

2) Can a no match return: "$F5: N/A" ?

Thanks

Just a couple tweaks needed:

1) Can you seperate each match with "; "?

2) Can a no match return: "$F5: N/A" ?

Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 07 2023 03:36 PM

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?

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 07 2023 07:11 PM

Can you share some data and expected result?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 07 2023 08:56 PM

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!

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 07 2023 09:01 PM

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!

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 08 2023 12:30 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 08 2023 01:14 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 08 2023 04:59 AM

"*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))
)
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 08 2023 09:22 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 08 2023 09:23 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 08 2023 09:44 AM

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!

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 08 2023 10:58 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 08 2023 11:08 AM

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

Any suggestions?

Any suggestions?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 08 2023 11:43 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 08 2023 12:08 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 08 2023 01:39 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 08 2023 03:47 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Oct 08 2023 04:50 PM

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 ?

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

Additionally,

If formula not available,how about web service ?