Excel Fox
07-03-2011, 07:23 PM
Here's a way to look up multiple columns for a unique combination of values, where the resultant value is a unique numeric value. The formula used is a sumproduct of the text columns, and a single numeric column
=SUMPRODUCT(--(CombinationList!$A$2:$A$71=Display!$D$2),--(CombinationList!$B$2:$B$71=Display!$G$2),--(CombinationList!$C$2:$C$71=Display!$J$2),Combinat ionList!$D$2:$D$71)
=SUMPRODUCT(--(CombinationList!$A$2:$A$71=Display!$D$2),--(CombinationList!$B$2:$B$71=Display!$G$2),--(CombinationList!$C$2:$C$71=Display!$J$2),Combinat ionList!$D$2:$D$71)