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

[Cmoreno89]

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? 

Thanks in advance!

[#2]

@Cmoreno89 

Has this already been asked?

Formula for returning multiple exact matches - Microsoft Community Hub

[#3]

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.

[#4]

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.

[#5]

@Cmoreno89 

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.

[#6]

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

[#7]

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!

[#8]

@Cmoreno89 

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.