Oct 07 2021 04:46 PM
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:
Any suggestions?
Oct 07 2021 05:23 PM
Oct 07 2021 06:08 PM
Oct 07 2021 06:13 PM
Solution=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.
Oct 07 2021 06:19 PM
Oct 07 2021 06:13 PM
Solution=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.