# How can this formula return multiple matches instead of just 1?

Hi all,

I have a formula that searches another worksheet and only returns the first exact match. How can it be edited to return ALL exact matches? This is the formula:

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

\$F5 is the worksheet/name we are searching in (only cells A1:A2000).

\$G\$5 is the exact search phrase we are searching for.

Any suggestions?

7 Replies

# Re: How can this formula return multiple matches instead of just 1?

This was the best formula you cane up with:

=\$F5&": "&TEXTJOIN("; ",,IF(SUM(LEN(BYROW(INDIRECT("'"&\$F5&"'!A1:A2000"),LAMBDA(v,IF(v=\$G\$5,"A"&CELL("row",v),"")))))=0,"N/A",BYROW(INDIRECT("'"&\$F5&"'!A1:A2000"),LAMBDA(v,IF(v=\$G\$5,"A"&CELL("row",v),"")))))&IF(SUM(LEN(BYROW(INDIRECT("'"&\$F5&"'!A1:A2000"),LAMBDA(v,IF(v=\$G\$5,"A"&CELL("row",v),"")))))=0,""," ("&\$G\$5&");")

However, this formula only worked in the initial cell it was pasted into. When I copied it down the column, it returned multiple errors, some "#NUM!" errors and some "#N/A" errors.

# Re: How can this formula return multiple matches instead of just 1?

And before you ask, the formatting on my end is not the problem. I've double checked everything on my end, the \$ are exactly where they need to be in the formula. Not sure why its not searching everything properly as it should be doing. I am using excel on an android tablet by the way.

# Re: How can this formula return multiple matches instead of just 1?

I don't know why it doesn't work on your computer and i can't suggest a solution since i can't see what is on your screen. The suggested formula returns the expected results in my Excel for the web as you can see in the screenshot of the other discussion.

I found your question very interesting and i ended up with a formula which spills the results for several search values. The search values are in range G5:G8 in this example. In an intermediate step i pulled the data from all sheets (Tabelle17:Tabelle21 in this example) in the result sheet. This data is in range F11:J21 in the screenshot.

# Re: How can this formula return multiple matches instead of just 1?

Are you using multiple worksheets? I cant see your worksheets from your screenshot, it is all cutoff.

# Re: How can this formula return multiple matches instead of just 1?

After some troubleshooting, I realized that the worlsheets that had some #num! and #n/a errors were not being properly searched! Is there a way to bypass the errors by adjusting this current formula?

=\$F5&": "&TEXTJOIN("; ",,IF(SUM(LEN(BYROW(INDIRECT("'"&\$F5&"'!A1:A2000"),LAMBDA(v,IF(v=\$G\$5,"A"&CELL("row",v),"")))))=0,"N/A",BYROW(INDIRECT("'"&\$F5&"'!A1:A2000"),LAMBDA(v,IF(v=\$G\$5,"A"&CELL("row",v),"")))))&IF(SUM(LEN(BYROW(INDIRECT("'"&\$F5&"'!A1:A2000"),LAMBDA(v,IF(v=\$G\$5,"A"&CELL("row",v),"")))))=0,""," ("&\$G\$5&");")

Please let me know either way! Thank you for all your help!

# Re: How can this formula return multiple matches instead of just 1?

As variant

``````=LET(
col, "A1:A2000",
range, INDIRECT("'" & \$F\$5 & "'!" & col ),
f, TOCOL( (range=\$G\$5)*ROW(range),3),
output,
\$F\$5 &
": " &
TEXTJOIN("; ",, LEFT(col) & FILTER(f,f) ) &
" (" & \$G\$5 & ")",
IFERROR(output, "no match")
)``````

It shall be adjusted if column name is more than one character, but idea is the same.