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

        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.

    • Cmoreno89's avatar
      Cmoreno89
      Brass Contributor
      Oct 14, 2023
      Are you using multiple worksheets? I cant see your worksheets from your screenshot, it is all cutoff.

Resources