SOLVED

Using =filter function with excluding criteria from list

%3CLINGO-SUB%20id%3D%22lingo-sub-2679537%22%20slang%3D%22en-US%22%3EUsing%20%3Dfilter%20function%20with%20excluding%20criteria%20from%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2679537%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20use%20the%20%3Dfilter%20function%20to%20exclude%20all%20names%20that%20appear%20on%20a%20list%2C%20so%20if%20that%20list%20expands%20in%20the%20future%2C%20I%20can%20just%20add%20a%20name%20and%20it%20will%20filter%20out%20the%20additional%20names.%20i%20also%20have%20other%20criteria%20I'm%20using%20to%20filter.%20So%20in%20this%20example%2C%20I%20want%20to%3A%26nbsp%3B%3CBR%20%2F%3E1.%20Exclude%20all%20names%20from%20the%20exclusion%20list%3CBR%20%2F%3E2.%20Keep%20only%20those%20that%20are%2020%20years%20old%3CBR%20%2F%3E3.%20Keep%20only%20those%20that%20are%20from%20Des%20Moines%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rapkingb_1-1629816113464.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305379i5657EDF90F70BECB%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22rapkingb_1-1629816113464.png%22%20alt%3D%22rapkingb_1-1629816113464.png%22%20%2F%3E%3C%2FSPAN%3E%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-2679537%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2679737%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20%3Dfilter%20function%20with%20excluding%20criteria%20from%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2679737%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1135631%22%20target%3D%22_blank%22%3E%40rapkingb%3C%2FA%3E%26nbsp%3BLike%20this%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DFILTER(%24AW%248%3A%24AY%2452%2CISNA(MATCH(%24AW%248%3A%24AW%2452%2C%24AU%248%3A%24AU%2415%2C0))*(%24AX%248%3A%24AX%2452%3D20)*(%24AY%248%3A%24AY%2452%3D%22Des%20Moine%22))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

I am trying to use the =filter function to exclude all names that appear on a list, so if that list expands in the future, I can just add a name and it will filter out the additional names. i also have other criteria I'm using to filter. So in this example, I want to: 
1. Exclude all names from the exclusion list
2. Keep only those that are 20 years old
3. Keep only those that are from Des Moines

 

Is this possible?

rapkingb_1-1629816113464.png

 

 

2 Replies
best response confirmed by rapkingb (New Contributor)
Solution

@rapkingb Like this:

=FILTER($AW$8:$AY$52,ISNA(MATCH($AW$8:$AW$52,$AU$8:$AU$15,0))*($AX$8:$AX$52=20)*($AY$8:$AY$52="Des Moine"))
Excellent. Thank you very much!