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 the following problem. 

 

I'm attempting to search and find exact matches and return the cell number (only searching the A columns). Currently the formula below works but doesn't return multiple exact matches, only the first one. 

 

The following formula works but only returns 1 match:

 

=IFERROR($F5&": "&TEXTJOIN("; ", TRUE, IF(XMATCH($G$5, INDIRECT("'"&$F5&"'!A:A"), 0), "A"&XMATCH($G$5, INDIRECT("'"&$F5&"'!A:A"), 0)&" - "& $G$5&"; ", "")), $F5&": N/A")

 

What I'm attempting to do is have it return multiple matches, one after the other, seperated by a "; ".

 

$F5 = worksheet name

$G$5 = exact search phrase

 

And again, I'm only searching the A column for potential matches. How can we edit this formula to accommodate multiple matches? I should also mention that I'm using a mobile tablet version of excel, so we cant use vba or macros or arrays or what have you. Please help! Thank you!

 

excel
Excel on mobile
Formulas and Functions
office 365
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Oct 08, 2023

    Cmoreno89 

    "so we can't use VBA or macros or arrays or what have you"

    I am not convinced that statement is correct; my understanding is that Dynamic arrays, Lambda, Typescript, Python were all introduced as part of the push to achieve platform independence.

    As a 365 beta user I haven't used anything but array formulas for several years now.

     

    I would suggest using a 3D array to hold the relevant portion of column A across sheets and then something of the sort

    = LET(
    nameheader, TOROW(sheetName),
    located,    HSTACK(data)=target,
    rowIndex,   SEQUENCE(ROWS(located)),
    rowNumber,  BYCOL(IF(located, rowIndex, ""), LAMBDA(c, TEXTJOIN(";", , c))),
    FILTER(nameheader & ": " & rowNumber & " - " & target, LEN(rowNumber))
 )

     

  • 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 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.

         

Resources