Help to use a FILTER formula on a FILTER list result

%3CLINGO-SUB%20id%3D%22lingo-sub-2335233%22%20slang%3D%22en-US%22%3EHelp%20to%20use%20a%20FILTER%20formula%20on%20a%20FILTER%20list%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335233%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20enclosed%20a%20spreadsheet%20I%E2%80%99m%20working%20building.%20Cell%20A7%20is%20a%20FILTER%20formula.%20So%20that%20it%20looks%20at%20a%20table%20and%20matches%20what%E2%80%99s%20in%20Cell%20G2%20and%20it%20lists%20all%20the%20customers%20that%20go%20with%20that%20Sales%20Node.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20in%20columns%20B%20%26amp%3B%20C%2C%20I%20have%20an%20Indexed%20VLookup%20to%20match%20the%20customer%20name%20and%20market%20type.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20where%20I%E2%80%99m%20stumped%E2%80%A6%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20a%20formula%20for%20Cell%20D7%20that%20will%20take%20the%20information%20in%20columns%20A-C%20based%20on%20the%20selection%20from%20Cell%20G3.%20I%20have%20a%20formula%20in%20cell%20D7%2C%20but%20it%20exports%20the%20result%20in%20the%20cell%20below%20it%20and%20it%20doesn%E2%80%99t%20run%20through%20the%20whole%20list%20to%20create%20a%20new%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20objective%20is%20to%20have%20the%20user%20select%20a%20sales%20node%20and%20a%20market%20type%20in%20cells%20G2%20and%20G3%20and%20then%20create%20a%20list%20of%20customers%20that%20fit%20that%20criteria.%20I%20was%20successful%20on%20the%20first%20selection%20in%20columns%20A%20%E2%80%93%20C%2C%20so%20I%20thought%20I%E2%80%99d%20replicate%20the%20information%20in%20columns%20D%20%E2%80%93%20F%20and%20just%20hide%20A%20%E2%80%93%20C%20before%20I%20sent%20it%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2335233%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-2335293%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20use%20a%20FILTER%20formula%20on%20a%20FILTER%20list%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335293%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047921%22%20target%3D%22_blank%22%3E%40A_Cannady72%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20to%20me%20that%20you%20might%20be%20unaware%20that%20you%20can%20do%20the%20first%20FILTER%20with%20two%20or%20more%20criteria.%20That%20might%20be%20all%20you%20need.%20For%20example%3A%3C%2FP%3E%3CP%3E%3DFILTER(%7Brange_to_be_filtered%7D%2C(criterion1)*(criterion2)%2C%22Nothing%20meets%20criteria%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPutting%20an%20asterisk%20between%20the%20two%20criteria%20operates%20to%20say%20%22the%20combination%20of%20these%20criteria%22%20needs%20to%20be%20met%2C%20so%20(If%20I'm%20reading%20your%20spreadsheet%20accurately)%2C%20putting%20the%20formula%20below%20into%20Cell%20A7%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DFILTER(EK2%3AEK11958%2C%20(G2%3DEP2%3AEP11958)*(G3%3DER2%3AER11958))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Edoes%20the%20trick%20without%20the%20need%20for%20a%20secondary%20step.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2337465%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20use%20a%20FILTER%20formula%20on%20a%20FILTER%20list%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2337465%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047921%22%20target%3D%22_blank%22%3E%40A_Cannady72%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20view%20the%20revised%20Example%20and%20let%20us%20know%20if%20that%20is%20what%20you%20need.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2339678%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20use%20a%20FILTER%20formula%20on%20a%20FILTER%20list%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2339678%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047921%22%20target%3D%22_blank%22%3E%40A_Cannady72%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20you%20have%20to%20do%20is%20fill%20Sales%20Node%20cell%20G2%2C%20and%20Market%20cell%20G3.%3C%2FP%3E%3CP%3Ethen%20press%20Data%26gt%3BRefresh%20All.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1620620266197.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F279400iAFEBB9B15CB0AB6B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1620620266197.png%22%20alt%3D%22Yea_So_0-1620620266197.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%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have enclosed a spreadsheet I’m working building. Cell A7 is a FILTER formula. So that it looks at a table and matches what’s in Cell G2 and it lists all the customers that go with that Sales Node.

 

Then in columns B & C, I have an Indexed VLookup to match the customer name and market type.

 

This is where I’m stumped…

 

I need a formula for Cell D7 that will take the information in columns A-C based on the selection from Cell G3. I have a formula in cell D7, but it exports the result in the cell below it and it doesn’t run through the whole list to create a new list.

 

My objective is to have the user select a sales node and a market type in cells G2 and G3 and then create a list of customers that fit that criteria. I was successful on the first selection in columns A – C, so I thought I’d replicate the information in columns D – F and just hide A – C before I sent it out.

3 Replies

@A_Cannady72 

 

It seems to me that you might be unaware that you can do the first FILTER with two or more criteria. That might be all you need. For example:

=FILTER({range_to_be_filtered},(criterion1)*(criterion2),"Nothing meets criteria")

 

Putting an asterisk between the two criteria operates to say "the combination of these criteria" needs to be met, so (If I'm reading your spreadsheet accurately), putting the formula below into Cell A7

=FILTER(EK2:EK11958, (G2=EP2:EP11958)*(G3=ER2:ER11958))

does the trick without the need for a secondary step.

@A_Cannady72 

 

Please view the revised Example and let us know if that is what you need.

@A_Cannady72 

 

All you have to do is fill Sales Node cell G2, and Market cell G3.

then press Data>Refresh All.

Yea_So_0-1620620266197.png

 

 

Cheers