SOLVED

New Contributor

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

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

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

Problem: There are multiple matches.

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

Okay, I'll try adding helpers to the table to identify only one result.
best response confirmed by NikkiOx (New Contributor)
Solution

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

=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.

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

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