CODEWITHSUNDEEP
excelexcel-formula
Mariel
Mariel

Reputation: 103

Index/Sumproduct with 3 criteria

I am trying to lookup a value from another table based on a reference table.

See below my data sample:

SHEET 1 ("CalculationLiability"):

enter image description here

SHEET 2 ("KeyMetrics"):

enter image description here

In sheet 1, cell G7 I am trying to look up the value from Sheet 2 based on 3 criteria (supplier unique ID, type and season) I tried the following formula, but it is returning a #REF error. 

=INDEX(KeyMetrics!$F$6:$AS$100,
         SUMPRODUCT((KeyMetrics!$D$6:$D$39=CalculationLiability!$D7)*
                    (KeyMetrics!$E$6:$E$39=CalculationLiability!$G$6)*
                    (KeyMetrics!$F$5:$AS$5=CalculationLiability!$E7)))

Anyone knows what I am doing wrong here? I can get it to work for two criteria, but for three criteria it doesn't work. Any help or push into the right direction is much appreciated!

Upvotes: 0

Views: 75

Answers (1)

teylyn
teylyn

Reputation: 35915

The Index uses a multi column, multi row reference. That means that you need two additional arguments, one argument for row, and another for column.

Your formula currently only provides one additional argument. When you step through the formula with the Evaluate Formula tool you can see that in the last step.

enter image description here

You can use an Index with two Match functions. The first one to find the row, the second one to find the column.

=INDEX(KeyMetrics!$F$6:$AS$100,
 MATCH($D7&$G$6,INDEX(KeyMetrics!$D$6:$D$100&KeyMetrics!$E$6:$E$100,0),0),
 MATCH(CalculationLiability!E7,KeyMetrics!$F$5:$AS$5))

You can also use Sumproduct, but in that case, don't use Index.

=SUMPRODUCT(KeyMetrics!$F$6:$AS$39,
    (KeyMetrics!$D$6:$D$39=CalculationLiability!$D7)*
    (KeyMetrics!$E$6:$E$39=CalculationLiability!$G$6)*
    (KeyMetrics!$F$5:$AS$5=CalculationLiability!$E7))

Upvotes: 2

Related Questions