SOLVED

Create Formula that will review three pieces of criteria and return a value

%3CLINGO-SUB%20id%3D%22lingo-sub-2824925%22%20slang%3D%22en-US%22%3ECreate%20Formula%20that%20will%20review%20three%20pieces%20of%20criteria%20and%20return%20a%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2824925%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Evening%20Excel%20Experts%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20requesting%20your%20assistance%20after%20spending%20several%20hours%20trying%20to%20create%20a%20specific%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3EI%E2%80%99ve%20looked%20into%20V-Lookup%3B%20Index%20%26amp%3B%20Match%20and%20even%20X-Lookup%20%3CEM%3E(new%20to%20me)%3C%2FEM%3E.%20The%20issue%20is%20that%20I%20need%20a%20function%20that%20will%20search%203%20pieces%20of%20criteria%20and%20return%20a%20value.%26nbsp%3B%20Can%20you%20advise%20me%20on%20the%20best%20way%20to%20accomplish%20this%3F%26nbsp%3B%20Below%20are%20some%20of%20the%20specifics%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EIn%20the%20first%20Tab%20%3CEM%3E(Master%20Sheet)%3C%2FEM%3E%2C%20I%E2%80%99ve%20already%20defined%20the%20Name%20to%20Data4.%3C%2FLI%3E%3CLI%3EIn%20Tab%202%20%3CEM%3E(Store%20List)%3C%2FEM%3E%2C%20I%E2%80%99m%20working%20on%20Cell%20number%20B6.%26nbsp%3B%20The%20goal%20is%20to%20create%20a%20formula%20or%20find%20a%20function%20to%20look%20up%20B4%20%3CEM%3E(Brand%20Type)%2C%3C%2FEM%3E%20A6%20%3CEM%3E(Category%20Tag)%3C%2FEM%3E%20and%20A1%20%3CEM%3E(Partial%20Text%20in%20case%20the%20store%20name%20also%20contains%20a%20space%20or%20other%20words)%3C%2FEM%3E%20in%20this%20sheet%20to%20return%20the%20value%20in%20Column%20E%20of%20the%20Master%20Sheet.%20%26nbsp%3BThe%20Value%20will%20either%20be%200%20or%20100.%3C%2FLI%3E%3CLI%3EFor%20the%20partial%20text%2C%20I%E2%80%99ve%20been%20entering%20%E2%80%9C*%E2%80%9D%26amp%3BA1%26amp%3B%E2%80%9D*%E2%80%9D%20into%20the%20formulas.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EAny%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2824925%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2824988%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20Formula%20that%20will%20review%20three%20pieces%20of%20criteria%20and%20return%20a%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2824988%22%20slang%3D%22en-US%22%3EProblem%3A%20There%20are%20multiple%20matches.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2825034%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20Formula%20that%20will%20review%20three%20pieces%20of%20criteria%20and%20return%20a%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2825034%22%20slang%3D%22en-US%22%3EOkay%2C%20I'll%20try%20adding%20helpers%20to%20the%20table%20to%20identify%20only%20one%20result.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2825047%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20Formula%20that%20will%20review%20three%20pieces%20of%20criteria%20and%20return%20a%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2825047%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1179171%22%20target%3D%22_blank%22%3E%40NikkiOx%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((%24A6%3D'Master%20sheet'!%24B%242%3A%24B%24185107)*(B%244%3D'Master%20sheet'!%24A%242%3A%24A%24185107)*('Master%20sheet'!%24F%242%3A%24F%24185107%3D1)*('Master%20sheet'!%24E%242%3A%24E%24185107))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20this%20formula%20in%20B6%20and%20copy%20across%20range%20B6%3AF64.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20order%20to%20perform%20the%20partial%20match%20i%20added%20column%20F%20in%20Master%20sheet%20with%20the%20following%20formula%20in%20F2%20(copy%20down%20to%20F185107)%3A%3C%2FP%3E%3CP%3E%3DCOUNT(FIND('Store%20list'!%24A%241%2C%24C2))%3C%2FP%3E%3CP%3EThis%20formula%20needs%20to%20be%20entered%20as%20matrix%20with%20ctrl%2Bshift%2Benter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20there%20a%20duplicate%20entries%20in%20the%20Master%20sheet%2C%20the%20results%20returned%20by%20SUMPRODCUT%20formula%20are%200%2C%20100%20%2C%20200%2C%20300%20....%20up%20to%201000.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Good Evening Excel Experts,

 

I am requesting your assistance after spending several hours trying to create a specific formula. 

I’ve looked into V-Lookup; Index & Match and even X-Lookup (new to me). The issue is that I need a function that will search 3 pieces of criteria and return a value.  Can you advise me on the best way to accomplish this?  Below are some of the specifics:

 

  1. In the first Tab (Master Sheet), I’ve already defined the Name to Data4.
  2. In Tab 2 (Store List), I’m working on Cell number B6.  The goal is to create a formula or find a function to look up B4 (Brand Type), A6 (Category Tag) and A1 (Partial Text in case the store name also contains a space or other words) in this sheet to return the value in Column E of the Master Sheet.  The Value will either be 0 or 100.
  3. For the partial text, I’ve been entering “*”&A1&”*” into the formulas.

Any suggestions?

 

4 Replies
Problem: There are multiple matches.
Okay, I'll try adding helpers to the table to identify only one result.
best response confirmed by NikkiOx (New Contributor)
Solution

@NikkiOx 

=SUMPRODUCT(($A6='Master sheet'!$B$2:$B$185107)*(B$4='Master sheet'!$A$2:$A$185107)*('Master sheet'!$F$2:$F$185107=1)*('Master sheet'!$E$2:$E$185107))

 

I suggest this formula in B6 and copy across range B6:F64.

 

In order to perform the partial match i added column F in Master sheet with the following formula in F2 (copy down to F185107):

=COUNT(FIND('Store list'!$A$1,$C2))

This formula needs to be entered as matrix with ctrl+shift+enter.

 

As there a duplicate entries in the Master sheet, the results returned by SUMPRODCUT formula are 0, 100 , 200, 300 .... up to 1000.

 

Thank you! I will try this! I sincerely appreciate it!