Forum Discussion

Cmoreno89's avatar
Cmoreno89
Copper Contributor
Oct 14, 2023

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? 

 

Thanks in advance!

excel
Excel on mobile
Formulas and Functions

7 Replies

Sort By
    • Cmoreno89's avatar
      Cmoreno89
      Copper Contributor
      Oct 14, 2023
      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.
    • Cmoreno89's avatar
      Cmoreno89
      Copper Contributor
      Oct 14, 2023
      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.
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Oct 14, 2023

        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.

Resources