Forum Discussion

Hogstad_Raadgivning's avatar
Hogstad_Raadgivning
Iron Contributor
Feb 15, 2020
Solved

Excel filter function - dynamic include column

Hi,

 

Is it possible to have an dynamic include column, using a formule with the filter function?

 

=FILTER(Tabell242[Navn];Tabell242[D6]<>"";"No one")

 

Where Tabell242[D6] is build "D"&Cellvalue

Like in this picture, I would like to use the Value in H4 (6) to select my column for filter.

 

 

Best Regards

- Geir

 

 

 

excel

7 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 15, 2020

    Hogstad_Raadgivning 

    Geir, I didn't tested, but something like

    =FILTER(Tabell242[Navn];
   INDEX(Tabell242;;XMATCH("D" & H$4,Tabell242[#Headers],0))<>"";
   "No one")

    shall work

    • katialou's avatar
      katialou
      Copper Contributor
      Aug 09, 2020

      SergeiBaklan 

       

      Hello.

       

      I need the opinion of an excel specialist in using the filter function.

       

      I want from the search in 5 columns with numbers, on which dates or columns a specific number is included.

       

      I’m' using the filter function but it does not give me results that I want, it gives me an error.

       

      Can you tell me what I’m doing wrong?

       

      Thank you in advance

       

      I have attached the excel file

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Aug 09, 2020

        katialou 

        First, use frittered range and the filter of the same size (first starts from A1, another from B2).

        Second, as a filter it shall be used not a matrix but single column array with equivalents of TRUE and FALSE. 

        As variant that could be

        =FILTER(A2:G30,MMULT(--(B2:G30=12),SEQUENCE(6,1,1,0)))

        For the future, please ask new question with new conversation from here https://techcommunity.microsoft.com/t5/excel/bd-p/ExcelGeneral, that's not a good practice to mix different question in one thread.

Resources