Forum Discussion

Cmoreno89's avatar
Cmoreno89
Brass 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&": "&TE...
excel
Excel on mobile
Formulas and Functions
  • Cmoreno89's avatar
    Cmoreno89
    Brass 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
    Brass 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.

      • Cmoreno89's avatar
        Cmoreno89
        Brass Contributor
        Oct 15, 2023
        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!

Resources