SOLVED

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

Copper 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 (Copper 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!
1 best response

Accepted Solutions
best response confirmed by NikkiOx (Copper 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.

 

View solution in original post