SOLVED

Excel filter function - dynamic include column

%3CLINGO-SUB%20id%3D%22lingo-sub-1175728%22%20slang%3D%22en-US%22%3EExcel%20filter%20function%20-%20dynamic%20include%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1175728%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20have%20an%20dynamic%20include%20column%2C%20using%20a%20formule%20with%20the%20filter%20function%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DFILTER(Tabell242%5BNavn%5D%3BTabell242%5BD6%5D%26lt%3B%26gt%3B%22%22%3B%22No%20one%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%26nbsp%3BTabell242%5BD6%5D%20is%20build%20%22D%22%26amp%3BCellvalue%3C%2FP%3E%3CP%3ELike%20in%20this%20picture%2C%20I%20would%20like%20to%20use%20the%20Value%20in%20H4%20(6)%20to%20select%20my%20column%20for%20filter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22filter%20dynamic%20include.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171369i2C6BA0CDB22225FA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22filter%20dynamic%20include.PNG%22%20alt%3D%22filter%20dynamic%20include.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1175728%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1175768%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20filter%20function%20-%20dynamic%20include%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1175768%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%20and%20have%20a%20nice%20weekend%20too%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1175762%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20filter%20function%20-%20dynamic%20include%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1175762%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bit%20worked%20perfect.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20a%20nice%20weekend.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBR%3C%2FP%3E%3CP%3E%2FGeir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1175742%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20filter%20function%20-%20dynamic%20include%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1175742%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGeir%2C%20I%20didn't%20tested%2C%20but%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DFILTER(Tabell242%5BNavn%5D%3B%0A%20%20%20INDEX(Tabell242%3B%3BXMATCH(%22D%22%20%26amp%3B%20H%244%2CTabell242%5B%23Headers%5D%2C0))%26lt%3B%26gt%3B%22%22%3B%0A%20%20%20%22No%20one%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eshall%20work%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576355%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576355%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20the%20opinion%20of%20an%20excel%20specialist%20in%20using%20the%20filter%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20from%20the%20search%20in%205%20columns%20with%20numbers%2C%20on%20which%20dates%20or%20columns%20a%20specific%20number%20is%20included.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%E2%80%99m'%20using%20the%20filter%20function%20but%20it%20does%20not%20give%20me%20results%20that%20I%20want%2C%20it%20gives%20me%20an%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20tell%20me%20what%20I%E2%80%99m%20doing%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20the%20excel%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576688%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576688%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F753840%22%20target%3D%22_blank%22%3E%40katialou%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%2C%20use%20frittered%20range%20and%20the%20filter%20of%20the%20same%20size%20(first%20starts%20from%20A1%2C%20another%20from%20B2).%3C%2FP%3E%0A%3CP%3ESecond%2C%20as%20a%20filter%20it%20shall%20be%20used%20not%20a%20matrix%20but%20single%20column%20array%20with%20equivalents%20of%20TRUE%20and%20FALSE.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20that%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(A2%3AG30%2CMMULT(--(B2%3AG30%3D12)%2CSEQUENCE(6%2C1%2C1%2C0)))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFor%20the%20future%2C%20please%20ask%20new%20question%20with%20new%20conversation%20from%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fbd-p%2FExcelGeneral%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fbd-p%2FExcelGeneral%3C%2FA%3E%2C%20that's%20not%20a%20good%20practice%20to%20mix%20different%20question%20in%20one%20thread.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1579637%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1579637%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20help%20and%20your%20answer%20to%20my%20post!%3C%2FP%3E%3CP%3ESorry%20I%20was%20late%20to%20thank%20you%20and%20reply%20to%20your%20post.%20I%20wrote%20from%20here%20because%20my%20post%20request%20was%20not%20accepted%20on%20the%20main%20page%20of%20the%20forum.%3C%2FP%3E%3CP%3EI%20am%20grateful%20for%20your%20help.%3C%2FP%3E%3CP%3EIf%20you%20want%20you%20can%20visit%20my%20website%20to%20listen%20to%20beautiful%20music.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.friendsofmusic.gr%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.friendsofmusic.gr%2F%3C%2FA%3E%3C%2FP%3E%3CP%3EIn%20the%20coming%20days%20I%20am%20thinking%20of%20sending%20you%20a%20gift%20card%20in%20your%20email.%3C%2FP%3E%3CP%3EKatia%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1581927%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20filter%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1581927%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F753840%22%20target%3D%22_blank%22%3E%40katialou%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Katia%20-%20appreciate%2C%20thank%20you%2C%20that's%20very%20nice!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

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.

 

filter dynamic include.PNG

 

Best Regards

- Geir

 

 

 

7 Replies
Best Response confirmed by Geir Hogstad (Frequent Contributor)
Solution

@Geir Hogstad 

Geir, I didn't tested, but something like

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

shall work

Thank you @Sergei Baklan it worked perfect.

 

Have a nice weekend.

 

BR

/Geir

@Geir Hogstad , you are welcome and have a nice weekend too

@Sergei Baklan 

 

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

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

 

Hello

Thank you very much for your help and your answer to my post!

Sorry I was late to thank you and reply to your post. I wrote from here because my post request was not accepted on the main page of the forum.

I am grateful for your help.

If you want you can visit my website to listen to beautiful music.

https://www.friendsofmusic.gr/

In the coming days I am thinking of sending you a gift card in your email.

Katia

 

@Sergei Baklan 

@katialou 

Hi Katia - appreciate, thank you, that's very nice!