SOLVED

Filter a list, if the value is in an array.

%3CLINGO-SUB%20id%3D%22lingo-sub-2412068%22%20slang%3D%22en-US%22%3EFilter%20a%20list%2C%20if%20the%20value%20is%20in%20an%20array.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2412068%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20filter%20a%20liste%20if%20the%20values%20in%20that%20list%20is%20in%20an%20array.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20work%2C%20but%20I%20can%20not%20use%20it%2C%20because%20the%20array%20(B2%3AG32)%20is%20dynamic%20and%20generated%20with%20a%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DFILTER(Tabell1%5BProjects%5D%3BCOUNTIFS(B2%3AG32%3BTabell1%5BProjects%5D)%26gt%3B0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20the%20end%20result%20should%20be%20the%20same%20as%20the%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2412068%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2412508%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20a%20list%2C%20if%20the%20value%20is%20in%20an%20array.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2412508%22%20slang%3D%22en-US%22%3EIn%20your%20example%20the%20array%20B2%3AG32%20is%20not%20dynamic%20but%20assuming%20it%20is%20generated%20by%20a%20formula%20in%20B2%20then%20you%20can%20simply%20reference%20B2%23%20and%20the%20%22%23%22%20tells%20excel%20to%20use%20the%20entire%20'spill%20range'%3C%2FLINGO-BODY%3E
Regular Contributor

Hi,

 

I need to filter a liste if the values in that list is in an array. 

 

This work, but I can not use it, because the array (B2:G32) is dynamic and generated with a formula.

 

=FILTER(Tabell1[Projects];COUNTIFS(B2:G32;Tabell1[Projects])>0)

 

But the end result should be the same as the example.

 

Best regards

- Geir

2 Replies
In your example the array B2:G32 is not dynamic but assuming it is generated by a formula in B2 then you can simply reference B2# and the "#" tells excel to use the entire 'spill range'
best response confirmed by Geir Hogstad (Regular Contributor)
Solution

@mtarler 

 

I Had the same problem using a dynamic area. But solved it.

 

Concateated the text in the Search array to one string. Did a search for the text in the table in that string, and it worked. Both with an fiexed array, and an dynamic Array using B2#.

 

- Geir