3 or more criteria lookup

%3CLINGO-SUB%20id%3D%22lingo-sub-2813831%22%20slang%3D%22en-US%22%3E3%20or%20more%20criteria%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2813831%22%20slang%3D%22en-US%22%3E%3CP%3EHelp%20needing%20to%20create%20a%20formula%20that%20would%20automatically%20filter%20data%20on%20E13%20to%20E15%20based%203%20or%20more%20criteria%20as%20shown%20below%20and%20attached%20(excel%20is%20a%20mockup%20to%20keep%20actual%20work%20data%20private).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Echallenge%20I%20am%20having%20is%20that%20info%20on%20column%20B%20has%20a%20range%20that%20I%20can't%20use%20a%20Vlookup%20with%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22cef_06_3-1633450954799.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F315342iD5E05D0141D7BE69%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22cef_06_3-1633450954799.png%22%20alt%3D%22cef_06_3-1633450954799.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%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2813831%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2814231%22%20slang%3D%22en-US%22%3ERe%3A%203%20or%20more%20criteria%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2814231%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1175910%22%20target%3D%22_blank%22%3E%40cef_06%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%20I%20unmerged%20the%20cells%20in%20column%20A%20and%20repeated%20the%20ages%2C%20and%20I%20split%20the%20weight%20ranges%20into%20two%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2815170%22%20slang%3D%22en-US%22%3ERe%3A%203%20or%20more%20criteria%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2815170%22%20slang%3D%22en-US%22%3Ethank%20you!%20I%20suspected%20separating%20the%20weight%20range%20is%20a%20must%2C%20so%20will%20try%20and%20experiment%20with%20this.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2815571%22%20slang%3D%22en-US%22%3ERe%3A%203%20or%20more%20criteria%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2815571%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1175910%22%20target%3D%22_blank%22%3E%40cef_06%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20is%3A%3C%2FP%3E%3CPRE%3E%3DINDEX((%24B%242%3A%24E%244%2C%24B%245%3A%24E%247%2C%24B%248%3A%24E%2410)%2C%20MATCH(C13%2CVALUE(SUBSTITUTE(INDEX((%24B%242%3A%24B%244%2C%24B%245%3A%24B%247%2C%24B%248%3A%24B%2410)%2C%2C%2CMATCH(B13%2CFILTER(A2%3AA10%2C(A2%3AA10%26gt%3B1)*1)%2C0))%2CRIGHT(INDEX((%24B%242%3A%24B%244%2C%24B%245%3A%24B%247%2C%24B%248%3A%24B%2410)%2C%2C%2CMATCH(B13%2CFILTER(A2%3AA10%2C(A2%3AA10%26gt%3B1)*1)%2C0))%2C6)%2C))%2C1)%2CMATCH(%22Region%20%22%26amp%3BD13%2C%24B%241%3A%24E%241%2C0)%2CMATCH(B13%2CFILTER(A2%3AA10%2C(A2%3AA10%26gt%3B1)*1)%2C0))%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Yea_So_0-1633486314580.png%22%20style%3D%22width%3A%20684px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F315461i97E38A4988F49122%2Fimage-dimensions%2F684x429%3Fv%3Dv2%22%20width%3D%22684%22%20height%3D%22429%22%20role%3D%22button%22%20title%3D%22Yea_So_0-1633486314580.png%22%20alt%3D%22Yea_So_0-1633486314580.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Echeers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2817640%22%20slang%3D%22en-US%22%3ERe%3A%203%20or%20more%20criteria%20lookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2817640%22%20slang%3D%22en-US%22%3Eamazing!%20thanks%20so%20much!%3C%2FLINGO-BODY%3E
New Contributor

Help needing to create a formula that would automatically filter data on E13 to E15 based 3 or more criteria as shown below and attached (excel is a mockup to keep actual work data private). 

 

challenge I am having is that info on column B has a range that I can't use a Vlookup with it. 

cef_06_3-1633450954799.png

 

 

 

5 Replies

@cef_06 

See the attached version. I unmerged the cells in column A and repeated the ages, and I split the weight ranges into two columns.

thank you! I suspected separating the weight range is a must, so will try and experiment with this.

@cef_06 

 

The formula is:

=INDEX(($B$2:$E$4,$B$5:$E$7,$B$8:$E$10), MATCH(C13,VALUE(SUBSTITUTE(INDEX(($B$2:$B$4,$B$5:$B$7,$B$8:$B$10),,,MATCH(B13,FILTER(A2:A10,(A2:A10>1)*1),0)),RIGHT(INDEX(($B$2:$B$4,$B$5:$B$7,$B$8:$B$10),,,MATCH(B13,FILTER(A2:A10,(A2:A10>1)*1),0)),6),)),1),MATCH("Region "&D13,$B$1:$E$1,0),MATCH(B13,FILTER(A2:A10,(A2:A10>1)*1),0))

Yea_So_0-1633486314580.png

 

cheers

amazing! thanks so much!

@cef_06 

also i forgot to fix these ranges, you might want to fix them with a $ sign:

Yea_So_0-1633544865547.png