Forum Discussion

NareshTakashi's avatar
NareshTakashi
Copper Contributor
Nov 11, 2020
Solved

Excel Filter Function - Use Same Formula Across Multiple Cells

Hello, I’m looking for some guidance on using the Excel Filter function and making it so it available to use in other cells within the same column. I have the following formula that looks for a val...
excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Nov 11, 2020

    NareshTakashi 

    Conceptually, you appear to be looking to produce an array or arrays which, sadly, Excel does not traditionally support.  As was suggested, the TRANSPOSE function would allow your list of matched country names to spill horizontally, so allowing multiple lists to be stacked vertically.  This would, though, require the formula to be copied down.

    Dressed up, the same formula could be written in the form

    = LET(
  matches, ISNUMBER(SEARCH(@PartialName, Map[Name])),
  matchingNames, FILTER(Map[Name], matches,"Not Found"),
  TRANSPOSE(matchingNames) )
JMB17's avatar
JMB17
Bronze Contributor
Nov 12, 2020

I don't have the FILTER or SORT functions, so I can't test with those particular functions. But, you might try using textjoin to join your arrays and then filter again:

=FILTERXML("<L><I>"&TEXTJOIN("</I><I>",TRUE,IF(ISNUMBER(SEARCH(TRANSPOSE(A1:A2),MAP[Name])),MAP[Name],""))&"</I></L>","//I")

And, you'll likely need to wrap it with iferror (or some conditional test) in case none of the items exist.

Resources