Forum Discussion

Cmoreno89's avatar
Cmoreno89
Brass Contributor
Oct 07, 2023

Formula for returning multiple exact matches

  Hi all,   I've been working on a formula for several days now but haven't been able to fully figure it out.  ChatGPT helped me get to this point but it can't figure out how to completely solve ...
excel
Excel on mobile
Formulas and Functions
office 365
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Oct 07, 2023

Cmoreno89 

=F5&": "&TEXTJOIN(",",,BYROW(INDIRECT("'"&$F5&"'!A:A"),
LAMBDA(v,IF(v=$G$5,"A"&CELL("row",v),""))))&" - "&$G$5

 

This returns the intended result in my sheet if i correctly understand what you want to do.

 

  • Cmoreno89's avatar
    Cmoreno89
    Brass Contributor
    Oct 07, 2023
    Is there also an alternate formula that calculates faster? It's taking ages to run through everything it seems...

    And also, when I copy and pasted the formula down it down into the rest of the F column there were some "#N/A" and "#NUM!" errors. How could this be?
    • JosWoolley's avatar
      JosWoolley
      Iron Contributor
      Oct 08, 2023
      Searching the entirety of column A would seem to be the cause of the problem with respect to calculation time. Do you really need to parse every cell up until row 1,048,576?
      • Cmoreno89's avatar
        Cmoreno89
        Brass Contributor
        Oct 08, 2023
        No actually, but every worksheet has a different amount of A rows being used. I guess we can adjust it to the largest number to save time?
    • peiyezhu's avatar
      peiyezhu
      Bronze Contributor
      Oct 08, 2023
      Can you share some data and expected result?
      • Cmoreno89's avatar
        Cmoreno89
        Brass Contributor
        Oct 08, 2023
        Oh and we are looking for EXACT matches only. And if there are no matches, I'd like it to return:
        "$F5: N/A"

        And if there are multiple matches, I'd like it to return each match like this:
        "$F5: <cell number found>; <cell number found>; <cell number found>; - <search phrase>"

        Hope that makes sense. If you have any other questions please ask. Thanks!
  • Cmoreno89's avatar
    Cmoreno89
    Brass Contributor
    Oct 07, 2023
    Thank you,

    Just a couple tweaks needed:
    1) Can you seperate each match with "; "?
    2) Can a no match return: "$F5: N/A" ?

    Thanks
    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Oct 08, 2023

      Cmoreno89 

      =F5&": "&TEXTJOIN(";",,IF(SUM(LEN(BYROW(INDIRECT("'"&$F5&"'!A:A"),
      LAMBDA(v,IF(v=$G$5,"A"&CELL("row",v),"")))))=0,"NA",BYROW(INDIRECT("'"&$F5&"'!A:A"),
      LAMBDA(v,IF(v=$G$5,"A"&CELL("row",v),"")))))&" - "&$G$5

       

      You are welcome. This formula uses ; instead of , and returns NA if no exact match is found. JosWoolley has explained why calculation might take some time, thank you.

       

      • Cmoreno89's avatar
        Cmoreno89
        Brass Contributor
        Oct 08, 2023
        Thank you!

        Just need 1 more tweak! Can you please make it so that when there is no match it returns: "$F5: N/A" instead of "$F5: N/A - $G$5". ? Thanks!

Resources