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) )
mtarler's avatar
mtarler
Silver Contributor
Nov 11, 2020

NareshTakashi  I don't understand what you mean by "work for the rows after A1".  Do you mean you want to have a filtered list for each value in column A or 1 filtered list base on a combination of values in A or something else?

So to have a list for each value in A you have the spillage problem but you could use TRANSPOSE() around your formula so instead of spilling down the column it spills to the right across columns

So to incorporate multiple options to create a combined filtered list you can:

 

 

=SORT(FILTER(Map[Name],ISNUMBER(SEARCH(Item!A1, Map[Name]))+ISNUMBER(SEARCH(Item!A2, Map[Name])),"Not Found"))

 

 

that is relatively easy to read but if you start to have a lot of items in column A that can get very large so you can also do this:

 

 

=FILTER(Map[Name],TRANSPOSE(MMULT(SEQUENCE(1,ROWS(A1:A3),1,0),--ISNUMBER(SEARCH(A1:A3,TRANSPOSE(CHOOSE(SEQUENCE(1,ROWS(A1:A3),1,0),Map[Name])))))),"none")

 

 

This is for 3 cells in A but can be expanded to larger number of rows in A.  NOTE: this will include any Name in Map that has any value in the col A range anywhere in that Name so if ANY included cell in the col A range is blank it will return all of the Names

Resources