Excel Multiple Company KPI lookup

[Biz]

Dear All,

I have constructed the formula for Company A and it works but it fails to work for other companies.
I used formulas in Col K-L and Should Be col P-R.

Your help would be greatly appreciated.

Values
Excel 2013 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​	R​
1​	Conditional Formatting Criteria		Criteria​						Company	KPI %	Red	Amber	Green		Should Be	Red	Amber	Green
2​	Company	KPI	Low Bound	Upper Bound	Colour Description				A	26%​	TRUE​	FALSE​	FALSE​			TRUE​	FALSE​	FALSE​
3​	A​	KPA %	75%​	100%​	Red				B	91%​	FALSE​	FALSE​	TRUE​			FALSE​	FALSE​	TRUE​
4​	A​		80%​	85%​	Amber				C	9%​	TRUE​	FALSE​	FALSE​			TRUE​	FALSE​	FALSE​
5​	A​		85%​	100%​	Green				D	18%​	TRUE​	FALSE​	FALSE​			TRUE​	FALSE​	FALSE​
6​	B​	KPA %	55%​	100%​	Red				A	80%​	FALSE​	TRUE​	FALSE​			FALSE​	TRUE​	FALSE​
7​	B​		60%​	65%​	Amber				B	70%​	TRUE​	FALSE​	FALSE​			FALSE​	FALSE​	TRUE​
8​	B​		65%​	100%​	Green				C	110%​	TRUE​	FALSE​	FALSE​			TRUE​	FALSE​	FALSE​
9​	C​	KPA %	40%​	100%​	Red				D	60%​	TRUE​	FALSE​	FALSE​			FALSE​	FALSE​	TRUE​
10​	C​		45%​	50%​	Amber				A	71%​	TRUE​	FALSE​	FALSE​			TRUE​	FALSE​	FALSE​
11​	C​		50%​	100%​	Green				B	9%​	TRUE​	FALSE​	FALSE​			TRUE​	FALSE​	FALSE​
12​	D​	KPA %	45%​	100%​	Red				C	99%​	FALSE​	FALSE​	TRUE​			FALSE​	FALSE​	TRUE​
13​	D​		50%​	55%​	Amber				D	38%​	TRUE​	FALSE​	FALSE​			TRUE​	FALSE​	FALSE​
14​	D​		55%​	100%​	Green				A	16%​	TRUE​	FALSE​	FALSE​			TRUE​	FALSE​	FALSE​
15​	E​	KPA %	50%​	100%​	Red				B	23%​	TRUE​	FALSE​	FALSE​			TRUE​	FALSE​	FALSE​
16​	E​		55%​	60%​	Amber				C	66%​	TRUE​	FALSE​	FALSE​			FALSE​	FALSE​	TRUE​
17​	E​		60%​	100%​	Green				D	59%​	TRUE​	FALSE​	FALSE​			FALSE​	FALSE​	TRUE​
18​	F​	KPA %	45%​	100%​	Red				A	92%​	FALSE​	FALSE​	TRUE​			FALSE​	FALSE​	TRUE​
19​	F​		50%​	55%​	Amber				B	100%​	FALSE​	FALSE​	TRUE​			FALSE​	FALSE​	TRUE​
20​	F​		55%​	100%​	Green				C	61%​	TRUE​	FALSE​	FALSE​			FALSE​	FALSE​	TRUE​
21​									D	95%​	FALSE​	FALSE​	TRUE​			FALSE​	FALSE​	TRUE​
22​									E	61%​	TRUE​	FALSE​	FALSE​			FALSE​	FALSE​	TRUE​
23​									F	95%​	FALSE​	FALSE​	TRUE​			FALSE​	FALSE​	TRUE​
24​									E	56%​	TRUE​	FALSE​	FALSE​			FALSE​	TRUE​	FALSE​
25​									F	53%​	TRUE​	FALSE​	FALSE​			FALSE​	TRUE​	FALSE​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Formulas Used
Excel 2013 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​	R​
1​	Conditional Formatting Criteria		Criteria​						Company	KPI %	Red	Amber	Green		Should Be	Red	Amber	Green
2​	Company	KPI	Low Bound	Upper Bound	Colour Description				A	26%​	=OR(AND(J2<>0,OR(J2<=$C$3,J2<$C$4)),J2>$D$3)​	=AND(J2>=$C$4,J2<=$D$4)​	=AND(J2>=$C$5,J2<=$D$5)​			TRUE​	FALSE​	FALSE​
3​	A​	KPA %	=C4-5%​	100%​	Red				B	91%​	=OR(AND(J3<>0,OR(J3<=$C$3,J3<$C$4)),J3>$D$3)​	=AND(J3>=$C$4,J3<=$D$4)​	=AND(J3>=$C$5,J3<=$D$5)​			FALSE​	FALSE​	TRUE​
4​	A​		=C5-5%​	=C5-0.01%​	Amber				C	9%​	=OR(AND(J4<>0,OR(J4<=$C$3,J4<$C$4)),J4>$D$3)​	=AND(J4>=$C$4,J4<=$D$4)​	=AND(J4>=$C$5,J4<=$D$5)​			TRUE​	FALSE​	FALSE​
5​	A​		85%​	=D3​	Green				D	18%​	=OR(AND(J5<>0,OR(J5<=$C$3,J5<$C$4)),J5>$D$3)​	=AND(J5>=$C$4,J5<=$D$4)​	=AND(J5>=$C$5,J5<=$D$5)​			TRUE​	FALSE​	FALSE​
6​	B​	KPA %	=C7-5%​	100%​	Red				A	80%​	=OR(AND(J6<>0,OR(J6<=$C$3,J6<$C$4)),J6>$D$3)​	=AND(J6>=$C$4,J6<=$D$4)​	=AND(J6>=$C$5,J6<=$D$5)​			FALSE​	TRUE​	FALSE​
7​	B​		=C8-5%​	=C8-0.01%​	Amber				B	70%​	=OR(AND(J7<>0,OR(J7<=$C$3,J7<$C$4)),J7>$D$3)​	=AND(J7>=$C$4,J7<=$D$4)​	=AND(J7>=$C$5,J7<=$D$5)​			FALSE​	FALSE​	TRUE​
8​	B​		65%​	=D6​	Green				C	110%​	=OR(AND(J8<>0,OR(J8<=$C$3,J8<$C$4)),J8>$D$3)​	=AND(J8>=$C$4,J8<=$D$4)​	=AND(J8>=$C$5,J8<=$D$5)​			TRUE​	FALSE​	FALSE​
9​	C​	KPA %	=C10-5%​	100%​	Red				D	60%​	=OR(AND(J9<>0,OR(J9<=$C$3,J9<$C$4)),J9>$D$3)​	=AND(J9>=$C$4,J9<=$D$4)​	=AND(J9>=$C$5,J9<=$D$5)​			FALSE​	FALSE​	TRUE​
10​	C​		=C11-5%​	=C11-0.01%​	Amber				A	71%​	=OR(AND(J10<>0,OR(J10<=$C$3,J10<$C$4)),J10>$D$3)​	=AND(J10>=$C$4,J10<=$D$4)​	=AND(J10>=$C$5,J10<=$D$5)​			TRUE​	FALSE​	FALSE​
11​	C​		50%​	=D9​	Green				B	9%​	=OR(AND(J11<>0,OR(J11<=$C$3,J11<$C$4)),J11>$D$3)​	=AND(J11>=$C$4,J11<=$D$4)​	=AND(J11>=$C$5,J11<=$D$5)​			TRUE​	FALSE​	FALSE​
12​	D​	KPA %	=C13-5%​	100%​	Red				C	99%​	=OR(AND(J12<>0,OR(J12<=$C$3,J12<$C$4)),J12>$D$3)​	=AND(J12>=$C$4,J12<=$D$4)​	=AND(J12>=$C$5,J12<=$D$5)​			FALSE​	FALSE​	TRUE​
13​	D​		=C14-5%​	=C14-0.01%​	Amber				D	38%​	=OR(AND(J13<>0,OR(J13<=$C$3,J13<$C$4)),J13>$D$3)​	=AND(J13>=$C$4,J13<=$D$4)​	=AND(J13>=$C$5,J13<=$D$5)​			TRUE​	FALSE​	FALSE​
14​	D​		55%​	=D12​	Green				A	16%​	=OR(AND(J14<>0,OR(J14<=$C$3,J14<$C$4)),J14>$D$3)​	=AND(J14>=$C$4,J14<=$D$4)​	=AND(J14>=$C$5,J14<=$D$5)​			TRUE​	FALSE​	FALSE​
15​	E​	KPA %	=C16-5%​	100%​	Red				B	23%​	=OR(AND(J15<>0,OR(J15<=$C$3,J15<$C$4)),J15>$D$3)​	=AND(J15>=$C$4,J15<=$D$4)​	=AND(J15>=$C$5,J15<=$D$5)​			TRUE​	FALSE​	FALSE​
16​	E​		=C17-5%​	=C17-0.01%​	Amber				C	66%​	=OR(AND(J16<>0,OR(J16<=$C$3,J16<$C$4)),J16>$D$3)​	=AND(J16>=$C$4,J16<=$D$4)​	=AND(J16>=$C$5,J16<=$D$5)​			FALSE​	FALSE​	TRUE​
17​	E​		60%​	=D15​	Green				D	59%​	=OR(AND(J17<>0,OR(J17<=$C$3,J17<$C$4)),J17>$D$3)​	=AND(J17>=$C$4,J17<=$D$4)​	=AND(J17>=$C$5,J17<=$D$5)​			FALSE​	FALSE​	TRUE​
18​	F​	KPA %	=C19-5%​	100%​	Red				A	92%​	=OR(AND(J18<>0,OR(J18<=$C$3,J18<$C$4)),J18>$D$3)​	=AND(J18>=$C$4,J18<=$D$4)​	=AND(J18>=$C$5,J18<=$D$5)​			FALSE​	FALSE​	TRUE​
19​	F​		=C20-5%​	=C20-0.01%​	Amber				B	100%​	=OR(AND(J19<>0,OR(J19<=$C$3,J19<$C$4)),J19>$D$3)​	=AND(J19>=$C$4,J19<=$D$4)​	=AND(J19>=$C$5,J19<=$D$5)​			FALSE​	FALSE​	TRUE​
20​	F​		55%​	=D18​	Green				C	61%​	=OR(AND(J20<>0,OR(J20<=$C$3,J20<$C$4)),J20>$D$3)​	=AND(J20>=$C$4,J20<=$D$4)​	=AND(J20>=$C$5,J20<=$D$5)​			FALSE​	FALSE​	TRUE​
21​									D	95%​	=OR(AND(J21<>0,OR(J21<=$C$3,J21<$C$4)),J21>$D$3)​	=AND(J21>=$C$4,J21<=$D$4)​	=AND(J21>=$C$5,J21<=$D$5)​			FALSE​	FALSE​	TRUE​
22​									E	61%​	=OR(AND(J22<>0,OR(J22<=$C$3,J22<$C$4)),J22>$D$3)​	=AND(J22>=$C$4,J22<=$D$4)​	=AND(J22>=$C$5,J22<=$D$5)​			FALSE​	FALSE​	TRUE​
23​									F	95%​	=OR(AND(J23<>0,OR(J23<=$C$3,J23<$C$4)),J23>$D$3)​	=AND(J23>=$C$4,J23<=$D$4)​	=AND(J23>=$C$5,J23<=$D$5)​			FALSE​	FALSE​	TRUE​
24​									E	56%​	=OR(AND(J24<>0,OR(J24<=$C$3,J24<$C$4)),J24>$D$3)​	=AND(J24>=$C$4,J24<=$D$4)​	=AND(J24>=$C$5,J24<=$D$5)​			FALSE​	TRUE​	FALSE​
25​									F	53%​	=OR(AND(J25<>0,OR(J25<=$C$3,J25<$C$4)),J25>$D$3)​	=AND(J25>=$C$4,J25<=$D$4)​	=AND(J25>=$C$5,J25<=$D$5)​			FALSE​	TRUE​	FALSE​

<tbody>
</tbody>

Sheet: Sheet1

<tbody>
</tbody>

Biz

 

[LateralThinker]

Hi

Try this:

Conditional Formatting Criteria

Criteria

Company

KPI %

Red

Amber

Green

Should Be

Red

Amber

Green

Company

KPI

Low Bound

Upper Bound

Colour Description

A

0.26

=OR(AND(J2<>0,OR(J2<=OFFSET($C$3,MATCH(I2,$A$3:$A$20,FALSE)-1,0),J2<OFFSET($C$4,MATCH(I2,$A$3:$A$20,FALSE)-1,0))),J2>OFFSET($D$3,MATCH(I2,$A$3:$A$20,FALSE)-1,0))

=AND(J2>=OFFSET($C$4,MATCH(I2,$A$3:$A$20,FALSE)-1,0),J2<=OFFSET($D$4,MATCH(I2,$A$3:$A$20,FALSE)-1,0))

=AND(J2>=OFFSET($C$5,MATCH(I2,$A$3:$A$20,FALSE)-1,0),J2<=OFFSET($D$5,MATCH(I2,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

A

KPA %

=C4-5%

1

Red

B

0.91

=OR(AND(J3<>0,OR(J3<=OFFSET($C$3,MATCH(I3,$A$3:$A$20,FALSE)-1,0),J3<OFFSET($C$4,MATCH(I3,$A$3:$A$20,FALSE)-1,0))),J3>OFFSET($D$3,MATCH(I3,$A$3:$A$20,FALSE)-1,0))

=AND(J3>=OFFSET($C$4,MATCH(I3,$A$3:$A$20,FALSE)-1,0),J3<=OFFSET($D$4,MATCH(I3,$A$3:$A$20,FALSE)-1,0))

=AND(J3>=OFFSET($C$5,MATCH(I3,$A$3:$A$20,FALSE)-1,0),J3<=OFFSET($D$5,MATCH(I3,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

A

=C5-5%

=C5-0.01%

Amber

C

0.09

=OR(AND(J4<>0,OR(J4<=OFFSET($C$3,MATCH(I4,$A$3:$A$20,FALSE)-1,0),J4<OFFSET($C$4,MATCH(I4,$A$3:$A$20,FALSE)-1,0))),J4>OFFSET($D$3,MATCH(I4,$A$3:$A$20,FALSE)-1,0))

=AND(J4>=OFFSET($C$4,MATCH(I4,$A$3:$A$20,FALSE)-1,0),J4<=OFFSET($D$4,MATCH(I4,$A$3:$A$20,FALSE)-1,0))

=AND(J4>=OFFSET($C$5,MATCH(I4,$A$3:$A$20,FALSE)-1,0),J4<=OFFSET($D$5,MATCH(I4,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

A

0.85

=D3

Green

D

0.18

=OR(AND(J5<>0,OR(J5<=OFFSET($C$3,MATCH(I5,$A$3:$A$20,FALSE)-1,0),J5<OFFSET($C$4,MATCH(I5,$A$3:$A$20,FALSE)-1,0))),J5>OFFSET($D$3,MATCH(I5,$A$3:$A$20,FALSE)-1,0))

=AND(J5>=OFFSET($C$4,MATCH(I5,$A$3:$A$20,FALSE)-1,0),J5<=OFFSET($D$4,MATCH(I5,$A$3:$A$20,FALSE)-1,0))

=AND(J5>=OFFSET($C$5,MATCH(I5,$A$3:$A$20,FALSE)-1,0),J5<=OFFSET($D$5,MATCH(I5,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

B

KPA %

=C7-5%

1

Red

A

0.8

=OR(AND(J6<>0,OR(J6<=OFFSET($C$3,MATCH(I6,$A$3:$A$20,FALSE)-1,0),J6<OFFSET($C$4,MATCH(I6,$A$3:$A$20,FALSE)-1,0))),J6>OFFSET($D$3,MATCH(I6,$A$3:$A$20,FALSE)-1,0))

=AND(J6>=OFFSET($C$4,MATCH(I6,$A$3:$A$20,FALSE)-1,0),J6<=OFFSET($D$4,MATCH(I6,$A$3:$A$20,FALSE)-1,0))

=AND(J6>=OFFSET($C$5,MATCH(I6,$A$3:$A$20,FALSE)-1,0),J6<=OFFSET($D$5,MATCH(I6,$A$3:$A$20,FALSE)-1,0))

FALSE

TRUE

FALSE

B

=C8-5%

=C8-0.01%

Amber

B

0.7

=OR(AND(J7<>0,OR(J7<=OFFSET($C$3,MATCH(I7,$A$3:$A$20,FALSE)-1,0),J7<OFFSET($C$4,MATCH(I7,$A$3:$A$20,FALSE)-1,0))),J7>OFFSET($D$3,MATCH(I7,$A$3:$A$20,FALSE)-1,0))

=AND(J7>=OFFSET($C$4,MATCH(I7,$A$3:$A$20,FALSE)-1,0),J7<=OFFSET($D$4,MATCH(I7,$A$3:$A$20,FALSE)-1,0))

=AND(J7>=OFFSET($C$5,MATCH(I7,$A$3:$A$20,FALSE)-1,0),J7<=OFFSET($D$5,MATCH(I7,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

B

0.65

=D6

Green

C

1.1

=OR(AND(J8<>0,OR(J8<=OFFSET($C$3,MATCH(I8,$A$3:$A$20,FALSE)-1,0),J8<OFFSET($C$4,MATCH(I8,$A$3:$A$20,FALSE)-1,0))),J8>OFFSET($D$3,MATCH(I8,$A$3:$A$20,FALSE)-1,0))

=AND(J8>=OFFSET($C$4,MATCH(I8,$A$3:$A$20,FALSE)-1,0),J8<=OFFSET($D$4,MATCH(I8,$A$3:$A$20,FALSE)-1,0))

=AND(J8>=OFFSET($C$5,MATCH(I8,$A$3:$A$20,FALSE)-1,0),J8<=OFFSET($D$5,MATCH(I8,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

C

KPA %

=C10-5%

1

Red

D

0.6

=OR(AND(J9<>0,OR(J9<=OFFSET($C$3,MATCH(I9,$A$3:$A$20,FALSE)-1,0),J9<OFFSET($C$4,MATCH(I9,$A$3:$A$20,FALSE)-1,0))),J9>OFFSET($D$3,MATCH(I9,$A$3:$A$20,FALSE)-1,0))

=AND(J9>=OFFSET($C$4,MATCH(I9,$A$3:$A$20,FALSE)-1,0),J9<=OFFSET($D$4,MATCH(I9,$A$3:$A$20,FALSE)-1,0))

=AND(J9>=OFFSET($C$5,MATCH(I9,$A$3:$A$20,FALSE)-1,0),J9<=OFFSET($D$5,MATCH(I9,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

C

=C11-5%

=C11-0.01%

Amber

A

0.71

=OR(AND(J10<>0,OR(J10<=OFFSET($C$3,MATCH(I10,$A$3:$A$20,FALSE)-1,0),J10<OFFSET($C$4,MATCH(I10,$A$3:$A$20,FALSE)-1,0))),J10>OFFSET($D$3,MATCH(I10,$A$3:$A$20,FALSE)-1,0))

=AND(J10>=OFFSET($C$4,MATCH(I10,$A$3:$A$20,FALSE)-1,0),J10<=OFFSET($D$4,MATCH(I10,$A$3:$A$20,FALSE)-1,0))

=AND(J10>=OFFSET($C$5,MATCH(I10,$A$3:$A$20,FALSE)-1,0),J10<=OFFSET($D$5,MATCH(I10,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

C

0.5

=D9

Green

B

0.09

=OR(AND(J11<>0,OR(J11<=OFFSET($C$3,MATCH(I11,$A$3:$A$20,FALSE)-1,0),J11<OFFSET($C$4,MATCH(I11,$A$3:$A$20,FALSE)-1,0))),J11>OFFSET($D$3,MATCH(I11,$A$3:$A$20,FALSE)-1,0))

=AND(J11>=OFFSET($C$4,MATCH(I11,$A$3:$A$20,FALSE)-1,0),J11<=OFFSET($D$4,MATCH(I11,$A$3:$A$20,FALSE)-1,0))

=AND(J11>=OFFSET($C$5,MATCH(I11,$A$3:$A$20,FALSE)-1,0),J11<=OFFSET($D$5,MATCH(I11,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

D

KPA %

=C13-5%

1

Red

C

0.99

=OR(AND(J12<>0,OR(J12<=OFFSET($C$3,MATCH(I12,$A$3:$A$20,FALSE)-1,0),J12<OFFSET($C$4,MATCH(I12,$A$3:$A$20,FALSE)-1,0))),J12>OFFSET($D$3,MATCH(I12,$A$3:$A$20,FALSE)-1,0))

=AND(J12>=OFFSET($C$4,MATCH(I12,$A$3:$A$20,FALSE)-1,0),J12<=OFFSET($D$4,MATCH(I12,$A$3:$A$20,FALSE)-1,0))

=AND(J12>=OFFSET($C$5,MATCH(I12,$A$3:$A$20,FALSE)-1,0),J12<=OFFSET($D$5,MATCH(I12,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

D

=C14-5%

=C14-0.01%

Amber

D

0.38

=OR(AND(J13<>0,OR(J13<=OFFSET($C$3,MATCH(I13,$A$3:$A$20,FALSE)-1,0),J13<OFFSET($C$4,MATCH(I13,$A$3:$A$20,FALSE)-1,0))),J13>OFFSET($D$3,MATCH(I13,$A$3:$A$20,FALSE)-1,0))

=AND(J13>=OFFSET($C$4,MATCH(I13,$A$3:$A$20,FALSE)-1,0),J13<=OFFSET($D$4,MATCH(I13,$A$3:$A$20,FALSE)-1,0))

=AND(J13>=OFFSET($C$5,MATCH(I13,$A$3:$A$20,FALSE)-1,0),J13<=OFFSET($D$5,MATCH(I13,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

D

0.55

=D12

Green

A

0.16

=OR(AND(J14<>0,OR(J14<=OFFSET($C$3,MATCH(I14,$A$3:$A$20,FALSE)-1,0),J14<OFFSET($C$4,MATCH(I14,$A$3:$A$20,FALSE)-1,0))),J14>OFFSET($D$3,MATCH(I14,$A$3:$A$20,FALSE)-1,0))

=AND(J14>=OFFSET($C$4,MATCH(I14,$A$3:$A$20,FALSE)-1,0),J14<=OFFSET($D$4,MATCH(I14,$A$3:$A$20,FALSE)-1,0))

=AND(J14>=OFFSET($C$5,MATCH(I14,$A$3:$A$20,FALSE)-1,0),J14<=OFFSET($D$5,MATCH(I14,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

E

KPA %

=C16-5%

1

Red

B

0.23

=OR(AND(J15<>0,OR(J15<=OFFSET($C$3,MATCH(I15,$A$3:$A$20,FALSE)-1,0),J15<OFFSET($C$4,MATCH(I15,$A$3:$A$20,FALSE)-1,0))),J15>OFFSET($D$3,MATCH(I15,$A$3:$A$20,FALSE)-1,0))

=AND(J15>=OFFSET($C$4,MATCH(I15,$A$3:$A$20,FALSE)-1,0),J15<=OFFSET($D$4,MATCH(I15,$A$3:$A$20,FALSE)-1,0))

=AND(J15>=OFFSET($C$5,MATCH(I15,$A$3:$A$20,FALSE)-1,0),J15<=OFFSET($D$5,MATCH(I15,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

E

=C17-5%

=C17-0.01%

Amber

C

0.66

=OR(AND(J16<>0,OR(J16<=OFFSET($C$3,MATCH(I16,$A$3:$A$20,FALSE)-1,0),J16<OFFSET($C$4,MATCH(I16,$A$3:$A$20,FALSE)-1,0))),J16>OFFSET($D$3,MATCH(I16,$A$3:$A$20,FALSE)-1,0))

=AND(J16>=OFFSET($C$4,MATCH(I16,$A$3:$A$20,FALSE)-1,0),J16<=OFFSET($D$4,MATCH(I16,$A$3:$A$20,FALSE)-1,0))

=AND(J16>=OFFSET($C$5,MATCH(I16,$A$3:$A$20,FALSE)-1,0),J16<=OFFSET($D$5,MATCH(I16,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

E

0.6

=D15

Green

D

0.59

=OR(AND(J17<>0,OR(J17<=OFFSET($C$3,MATCH(I17,$A$3:$A$20,FALSE)-1,0),J17<OFFSET($C$4,MATCH(I17,$A$3:$A$20,FALSE)-1,0))),J17>OFFSET($D$3,MATCH(I17,$A$3:$A$20,FALSE)-1,0))

=AND(J17>=OFFSET($C$4,MATCH(I17,$A$3:$A$20,FALSE)-1,0),J17<=OFFSET($D$4,MATCH(I17,$A$3:$A$20,FALSE)-1,0))

=AND(J17>=OFFSET($C$5,MATCH(I17,$A$3:$A$20,FALSE)-1,0),J17<=OFFSET($D$5,MATCH(I17,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

F

KPA %

=C19-5%

1

Red

A

0.92

=OR(AND(J18<>0,OR(J18<=OFFSET($C$3,MATCH(I18,$A$3:$A$20,FALSE)-1,0),J18<OFFSET($C$4,MATCH(I18,$A$3:$A$20,FALSE)-1,0))),J18>OFFSET($D$3,MATCH(I18,$A$3:$A$20,FALSE)-1,0))

=AND(J18>=OFFSET($C$4,MATCH(I18,$A$3:$A$20,FALSE)-1,0),J18<=OFFSET($D$4,MATCH(I18,$A$3:$A$20,FALSE)-1,0))

=AND(J18>=OFFSET($C$5,MATCH(I18,$A$3:$A$20,FALSE)-1,0),J18<=OFFSET($D$5,MATCH(I18,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

F

=C20-5%

=C20-0.01%

Amber

B

1

=OR(AND(J19<>0,OR(J19<=OFFSET($C$3,MATCH(I19,$A$3:$A$20,FALSE)-1,0),J19<OFFSET($C$4,MATCH(I19,$A$3:$A$20,FALSE)-1,0))),J19>OFFSET($D$3,MATCH(I19,$A$3:$A$20,FALSE)-1,0))

=AND(J19>=OFFSET($C$4,MATCH(I19,$A$3:$A$20,FALSE)-1,0),J19<=OFFSET($D$4,MATCH(I19,$A$3:$A$20,FALSE)-1,0))

=AND(J19>=OFFSET($C$5,MATCH(I19,$A$3:$A$20,FALSE)-1,0),J19<=OFFSET($D$5,MATCH(I19,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

F

0.55

=D18

Green

C

0.61

=OR(AND(J20<>0,OR(J20<=OFFSET($C$3,MATCH(I20,$A$3:$A$20,FALSE)-1,0),J20<OFFSET($C$4,MATCH(I20,$A$3:$A$20,FALSE)-1,0))),J20>OFFSET($D$3,MATCH(I20,$A$3:$A$20,FALSE)-1,0))

=AND(J20>=OFFSET($C$4,MATCH(I20,$A$3:$A$20,FALSE)-1,0),J20<=OFFSET($D$4,MATCH(I20,$A$3:$A$20,FALSE)-1,0))

=AND(J20>=OFFSET($C$5,MATCH(I20,$A$3:$A$20,FALSE)-1,0),J20<=OFFSET($D$5,MATCH(I20,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

D

0.95

=OR(AND(J21<>0,OR(J21<=OFFSET($C$3,MATCH(I21,$A$3:$A$20,FALSE)-1,0),J21<OFFSET($C$4,MATCH(I21,$A$3:$A$20,FALSE)-1,0))),J21>OFFSET($D$3,MATCH(I21,$A$3:$A$20,FALSE)-1,0))

=AND(J21>=OFFSET($C$4,MATCH(I21,$A$3:$A$20,FALSE)-1,0),J21<=OFFSET($D$4,MATCH(I21,$A$3:$A$20,FALSE)-1,0))

=AND(J21>=OFFSET($C$5,MATCH(I21,$A$3:$A$20,FALSE)-1,0),J21<=OFFSET($D$5,MATCH(I21,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

E

0.61

=OR(AND(J22<>0,OR(J22<=OFFSET($C$3,MATCH(I22,$A$3:$A$20,FALSE)-1,0),J22<OFFSET($C$4,MATCH(I22,$A$3:$A$20,FALSE)-1,0))),J22>OFFSET($D$3,MATCH(I22,$A$3:$A$20,FALSE)-1,0))

=AND(J22>=OFFSET($C$4,MATCH(I22,$A$3:$A$20,FALSE)-1,0),J22<=OFFSET($D$4,MATCH(I22,$A$3:$A$20,FALSE)-1,0))

=AND(J22>=OFFSET($C$5,MATCH(I22,$A$3:$A$20,FALSE)-1,0),J22<=OFFSET($D$5,MATCH(I22,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

F

0.95

=OR(AND(J23<>0,OR(J23<=OFFSET($C$3,MATCH(I23,$A$3:$A$20,FALSE)-1,0),J23<OFFSET($C$4,MATCH(I23,$A$3:$A$20,FALSE)-1,0))),J23>OFFSET($D$3,MATCH(I23,$A$3:$A$20,FALSE)-1,0))

=AND(J23>=OFFSET($C$4,MATCH(I23,$A$3:$A$20,FALSE)-1,0),J23<=OFFSET($D$4,MATCH(I23,$A$3:$A$20,FALSE)-1,0))

=AND(J23>=OFFSET($C$5,MATCH(I23,$A$3:$A$20,FALSE)-1,0),J23<=OFFSET($D$5,MATCH(I23,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

E

0.56

=OR(AND(J24<>0,OR(J24<=OFFSET($C$3,MATCH(I24,$A$3:$A$20,FALSE)-1,0),J24<OFFSET($C$4,MATCH(I24,$A$3:$A$20,FALSE)-1,0))),J24>OFFSET($D$3,MATCH(I24,$A$3:$A$20,FALSE)-1,0))

=AND(J24>=OFFSET($C$4,MATCH(I24,$A$3:$A$20,FALSE)-1,0),J24<=OFFSET($D$4,MATCH(I24,$A$3:$A$20,FALSE)-1,0))

=AND(J24>=OFFSET($C$5,MATCH(I24,$A$3:$A$20,FALSE)-1,0),J24<=OFFSET($D$5,MATCH(I24,$A$3:$A$20,FALSE)-1,0))

FALSE

TRUE

FALSE

F

0.53

=OR(AND(J25<>0,OR(J25<=OFFSET($C$3,MATCH(I25,$A$3:$A$20,FALSE)-1,0),J25<OFFSET($C$4,MATCH(I25,$A$3:$A$20,FALSE)-1,0))),J25>OFFSET($D$3,MATCH(I25,$A$3:$A$20,FALSE)-1,0))

=AND(J25>=OFFSET($C$4,MATCH(I25,$A$3:$A$20,FALSE)-1,0),J25<=OFFSET($D$4,MATCH(I25,$A$3:$A$20,FALSE)-1,0))

=AND(J25>=OFFSET($C$5,MATCH(I25,$A$3:$A$20,FALSE)-1,0),J25<=OFFSET($D$5,MATCH(I25,$A$3:$A$20,FALSE)-1,0))

<tbody>
</tbody><colgroup><col><col><col><col><col><col span="3"><col><col><col><col><col><col><col><col span="3"></colgroup>

 

[jorismoerings]

Alternative:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Conditional Formatting Criteria		Criteria						Company	KPI %	Red	Amber	Green		Should Be	Red	Amber	Green
2	Company	KPI	Low Bound	Upper Bound	Colour Description				A	26%	TRUE	FALSE	FALSE			TRUE	FALSE	FALSE
3	A	KPA %	75%	100%	Red				B	91%	FALSE	FALSE	TRUE			FALSE	FALSE	TRUE
4	A		80%	85%	Amber				C	9%	TRUE	FALSE	FALSE			TRUE	FALSE	FALSE
5	A		85%	100%	Green				D	18%	TRUE	FALSE	FALSE			TRUE	FALSE	FALSE
6	B	KPA %	55%	100%	Red				A	80%	FALSE	TRUE	FALSE			FALSE	TRUE	FALSE
7	B		60%	65%	Amber				B	70%	FALSE	FALSE	TRUE			FALSE	FALSE	TRUE
8	B		65%	100%	Green				C	110%	TRUE	FALSE	FALSE			TRUE	FALSE	FALSE
9	C	KPA %	40%	100%	Red				D	60%	FALSE	FALSE	TRUE			FALSE	FALSE	TRUE
10	C		45%	50%	Amber				A	71%	TRUE	FALSE	FALSE			TRUE	FALSE	FALSE
11	C		50%	100%	Green				B	9%	TRUE	FALSE	FALSE			TRUE	FALSE	FALSE
12	D	KPA %	45%	100%	Red				C	99%	FALSE	FALSE	TRUE			FALSE	FALSE	TRUE
13	D		50%	55%	Amber				D	38%	TRUE	FALSE	FALSE			TRUE	FALSE	FALSE
14	D		55%	100%	Green				A	16%	TRUE	FALSE	FALSE			TRUE	FALSE	FALSE
15	E	KPA %	50%	100%	Red				B	23%	TRUE	FALSE	FALSE			TRUE	FALSE	FALSE
16	E		55%	60%	Amber				C	66%	FALSE	FALSE	TRUE			FALSE	FALSE	TRUE
17	E		60%	100%	Green				D	59%	FALSE	FALSE	TRUE			FALSE	FALSE	TRUE
18	F	KPA %	45%	100%	Red				A	92%	FALSE	FALSE	TRUE			FALSE	FALSE	TRUE
19	F		50%	55%	Amber				B	100%	FALSE	FALSE	TRUE			FALSE	FALSE	TRUE
20	F		55%	100%	Green				C	61%	FALSE	FALSE	TRUE			FALSE	FALSE	TRUE
21									D	95%	FALSE	FALSE	TRUE			FALSE	FALSE	TRUE
22									E	61%	FALSE	FALSE	TRUE			FALSE	FALSE	TRUE
23									F	95%	FALSE	FALSE	TRUE			FALSE	FALSE	TRUE
24									E	56%	FALSE	TRUE	FALSE			FALSE	TRUE	FALSE
25									F	53%	FALSE	TRUE	FALSE			FALSE	TRUE	FALSE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula K2 =AND($J2<>0,OR($J2<SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Red")*($C$3:$C$20)),$J2>SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Red")*($D$3:$D$20)))) L2 =AND($J2>=SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Amber")*($C$3:$C$20)),$J2<=SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Amber")*($D$3:$D$20))) M2 =AND($J2>=SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Green")*($C$3:$C$20)),$J2<=SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Green")*($D$3:$D$20))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

@ lateralthinker: your formula is missing the operator for the RED column:
=OR(AND(J2<>0,OR(J2<=OFFSET($C$3,MATCH(I2,$A$3:$A$20,FALSE)-1,0),J2>OFFSET($D$3,MATCH(I2,$A$3:$A$20,FALSE)-1,0))

 

[LateralThinker]

Sorry for my fomulas mistake.

I modified them.

thanks

Hi

Try this:

Conditional Formatting Criteria

Criteria

Company

KPI %

Red

Amber

Green

Should Be

Red

Amber

Green

Company

KPI

Low Bound

Upper Bound

Colour Description

A

0.26

=OR(AND(J2<>0,OR(J2<=OFFSET($C$3,MATCH(I2,$A$3:$A$20,FALSE)-1,0),J2><OFFSET($C$4,MATCH(I2,$A$3:$a$20,false)-1,0))),j2>OFFSET($D$3,MATCH(I2,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I2,$A$3:$a$20,false)-1,0))),j2>

=AND(J2>=OFFSET($C$4,MATCH(I2,$A$3:$A$20,FALSE)-1,0),J2<=OFFSET($D$4,MATCH(I2,$A$3:$A$20,FALSE)-1,0))

=AND(J2>=OFFSET($C$5,MATCH(I2,$A$3:$A$20,FALSE)-1,0),J2<=OFFSET($D$5,MATCH(I2,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

A

KPA %

=C4-5%

1

Red

B

0.91

=OR(AND(J3<>0,OR(J3<=OFFSET($C$3,MATCH(I3,$A$3:$A$20,FALSE)-1,0),J3><OFFSET($C$4,MATCH(I3,$A$3:$a$20,false)-1,0))),j3>OFFSET($D$3,MATCH(I3,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I3,$A$3:$a$20,false)-1,0))),j3>

=AND(J3>=OFFSET($C$4,MATCH(I3,$A$3:$A$20,FALSE)-1,0),J3<=OFFSET($D$4,MATCH(I3,$A$3:$A$20,FALSE)-1,0))

=AND(J3>=OFFSET($C$5,MATCH(I3,$A$3:$A$20,FALSE)-1,0),J3<=OFFSET($D$5,MATCH(I3,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

A

=C5-5%

=C5-0.01%

Amber

C

0.09

=OR(AND(J4<>0,OR(J4<=OFFSET($C$3,MATCH(I4,$A$3:$A$20,FALSE)-1,0),J4><OFFSET($C$4,MATCH(I4,$A$3:$a$20,false)-1,0))),j4>OFFSET($D$3,MATCH(I4,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I4,$A$3:$a$20,false)-1,0))),j4>

=AND(J4>=OFFSET($C$4,MATCH(I4,$A$3:$A$20,FALSE)-1,0),J4<=OFFSET($D$4,MATCH(I4,$A$3:$A$20,FALSE)-1,0))

=AND(J4>=OFFSET($C$5,MATCH(I4,$A$3:$A$20,FALSE)-1,0),J4<=OFFSET($D$5,MATCH(I4,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

A

0.85

=D3

Green

D

0.18

=OR(AND(J5<>0,OR(J5<=OFFSET($C$3,MATCH(I5,$A$3:$A$20,FALSE)-1,0),J5><OFFSET($C$4,MATCH(I5,$A$3:$a$20,false)-1,0))),j5>OFFSET($D$3,MATCH(I5,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I5,$A$3:$a$20,false)-1,0))),j5>

=AND(J5>=OFFSET($C$4,MATCH(I5,$A$3:$A$20,FALSE)-1,0),J5<=OFFSET($D$4,MATCH(I5,$A$3:$A$20,FALSE)-1,0))

=AND(J5>=OFFSET($C$5,MATCH(I5,$A$3:$A$20,FALSE)-1,0),J5<=OFFSET($D$5,MATCH(I5,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

B

KPA %

=C7-5%

1

Red

A

0.8

=OR(AND(J6<>0,OR(J6<=OFFSET($C$3,MATCH(I6,$A$3:$A$20,FALSE)-1,0),J6><OFFSET($C$4,MATCH(I6,$A$3:$a$20,false)-1,0))),j6>OFFSET($D$3,MATCH(I6,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I6,$A$3:$a$20,false)-1,0))),j6>

=AND(J6>=OFFSET($C$4,MATCH(I6,$A$3:$A$20,FALSE)-1,0),J6<=OFFSET($D$4,MATCH(I6,$A$3:$A$20,FALSE)-1,0))

=AND(J6>=OFFSET($C$5,MATCH(I6,$A$3:$A$20,FALSE)-1,0),J6<=OFFSET($D$5,MATCH(I6,$A$3:$A$20,FALSE)-1,0))

FALSE

TRUE

FALSE

B

=C8-5%

=C8-0.01%

Amber

B

0.7

=OR(AND(J7<>0,OR(J7<=OFFSET($C$3,MATCH(I7,$A$3:$A$20,FALSE)-1,0),J7><OFFSET($C$4,MATCH(I7,$A$3:$a$20,false)-1,0))),j7>OFFSET($D$3,MATCH(I7,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I7,$A$3:$a$20,false)-1,0))),j7>

=AND(J7>=OFFSET($C$4,MATCH(I7,$A$3:$A$20,FALSE)-1,0),J7<=OFFSET($D$4,MATCH(I7,$A$3:$A$20,FALSE)-1,0))

=AND(J7>=OFFSET($C$5,MATCH(I7,$A$3:$A$20,FALSE)-1,0),J7<=OFFSET($D$5,MATCH(I7,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

B

0.65

=D6

Green

C

1.1

=OR(AND(J8<>0,OR(J8<=OFFSET($C$3,MATCH(I8,$A$3:$A$20,FALSE)-1,0),J8><OFFSET($C$4,MATCH(I8,$A$3:$a$20,false)-1,0))),j8>OFFSET($D$3,MATCH(I8,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I8,$A$3:$a$20,false)-1,0))),j8>

=AND(J8>=OFFSET($C$4,MATCH(I8,$A$3:$A$20,FALSE)-1,0),J8<=OFFSET($D$4,MATCH(I8,$A$3:$A$20,FALSE)-1,0))

=AND(J8>=OFFSET($C$5,MATCH(I8,$A$3:$A$20,FALSE)-1,0),J8<=OFFSET($D$5,MATCH(I8,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

C

KPA %

=C10-5%

1

Red

D

0.6

=OR(AND(J9<>0,OR(J9<=OFFSET($C$3,MATCH(I9,$A$3:$A$20,FALSE)-1,0),J9><OFFSET($C$4,MATCH(I9,$A$3:$a$20,false)-1,0))),j9>OFFSET($D$3,MATCH(I9,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I9,$A$3:$a$20,false)-1,0))),j9>

=AND(J9>=OFFSET($C$4,MATCH(I9,$A$3:$A$20,FALSE)-1,0),J9<=OFFSET($D$4,MATCH(I9,$A$3:$A$20,FALSE)-1,0))

=AND(J9>=OFFSET($C$5,MATCH(I9,$A$3:$A$20,FALSE)-1,0),J9<=OFFSET($D$5,MATCH(I9,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

C

=C11-5%

=C11-0.01%

Amber

A

0.71

=OR(AND(J10<>0,OR(J10<=OFFSET($C$3,MATCH(I10,$A$3:$A$20,FALSE)-1,0),J10><OFFSET($C$4,MATCH(I10,$A$3:$a$20,false)-1,0))),j10>OFFSET($D$3,MATCH(I10,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I10,$A$3:$a$20,false)-1,0))),j10>

=AND(J10>=OFFSET($C$4,MATCH(I10,$A$3:$A$20,FALSE)-1,0),J10<=OFFSET($D$4,MATCH(I10,$A$3:$A$20,FALSE)-1,0))

=AND(J10>=OFFSET($C$5,MATCH(I10,$A$3:$A$20,FALSE)-1,0),J10<=OFFSET($D$5,MATCH(I10,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

C

0.5

=D9

Green

B

0.09

=OR(AND(J11<>0,OR(J11<=OFFSET($C$3,MATCH(I11,$A$3:$A$20,FALSE)-1,0),J11<OFFSET($C$4,MATCH(I11,$A$3:$a$20,false)-1,0))),j11>O>FFSET($D$3,MATCH(I11,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I11,$A$3:$a$20,false)-1,0))),j11>

=AND(J11>=OFFSET($C$4,MATCH(I11,$A$3:$A$20,FALSE)-1,0),J11<=OFFSET($D$4,MATCH(I11,$A$3:$A$20,FALSE)-1,0))

=AND(J11>=OFFSET($C$5,MATCH(I11,$A$3:$A$20,FALSE)-1,0),J11<=OFFSET($D$5,MATCH(I11,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

D

KPA %

=C13-5%

1

Red

C

0.99

=OR(AND(J12<>0,OR(J12<=OFFSET($C$3,MATCH(I12,$A$3:$A$20,FALSE)-1,0),J12><OFFSET($C$4,MATCH(I12,$A$3:$a$20,false)-1,0))),j12>OFFSET($D$3,MATCH(I12,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I12,$A$3:$a$20,false)-1,0))),j12>

=AND(J12>=OFFSET($C$4,MATCH(I12,$A$3:$A$20,FALSE)-1,0),J12<=OFFSET($D$4,MATCH(I12,$A$3:$A$20,FALSE)-1,0))

=AND(J12>=OFFSET($C$5,MATCH(I12,$A$3:$A$20,FALSE)-1,0),J12<=OFFSET($D$5,MATCH(I12,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

D

=C14-5%

=C14-0.01%

Amber

D

0.38

=OR(AND(J13<>0,OR(J13<=OFFSET($C$3,MATCH(I13,$A$3:$A$20,FALSE)-1,0),J13><OFFSET($C$4,MATCH(I13,$A$3:$a$20,false)-1,0))),j13>OFFSET($D$3,MATCH(I13,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I13,$A$3:$a$20,false)-1,0))),j13>

=AND(J13>=OFFSET($C$4,MATCH(I13,$A$3:$A$20,FALSE)-1,0),J13<=OFFSET($D$4,MATCH(I13,$A$3:$A$20,FALSE)-1,0))

=AND(J13>=OFFSET($C$5,MATCH(I13,$A$3:$A$20,FALSE)-1,0),J13<=OFFSET($D$5,MATCH(I13,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

D

0.55

=D12

Green

A

0.16

=OR(AND(J14<>0,OR(J14<=OFFSET($C$3,MATCH(I14,$A$3:$A$20,FALSE)-1,0),J14><OFFSET($C$4,MATCH(I14,$A$3:$a$20,false)-1,0))),j14>OFFSET($D$3,MATCH(I14,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I14,$A$3:$a$20,false)-1,0))),j14>

=AND(J14>=OFFSET($C$4,MATCH(I14,$A$3:$A$20,FALSE)-1,0),J14<=OFFSET($D$4,MATCH(I14,$A$3:$A$20,FALSE)-1,0))

=AND(J14>=OFFSET($C$5,MATCH(I14,$A$3:$A$20,FALSE)-1,0),J14<=OFFSET($D$5,MATCH(I14,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

E

KPA %

=C16-5%

1

Red

B

0.23

=OR(AND(J15<>0,OR(J15<=OFFSET($C$3,MATCH(I15,$A$3:$A$20,FALSE)-1,0),J15><OFFSET($C$4,MATCH(I15,$A$3:$a$20,false)-1,0))),j15>OFFSET($D$3,MATCH(I15,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I15,$A$3:$a$20,false)-1,0))),j15>

=AND(J15>=OFFSET($C$4,MATCH(I15,$A$3:$A$20,FALSE)-1,0),J15<=OFFSET($D$4,MATCH(I15,$A$3:$A$20,FALSE)-1,0))

=AND(J15>=OFFSET($C$5,MATCH(I15,$A$3:$A$20,FALSE)-1,0),J15<=OFFSET($D$5,MATCH(I15,$A$3:$A$20,FALSE)-1,0))

TRUE

FALSE

FALSE

E

=C17-5%

=C17-0.01%

Amber

C

0.66

=OR(AND(J16<>0,OR(J16<=OFFSET($C$3,MATCH(I16,$A$3:$A$20,FALSE)-1,0),J16><OFFSET($C$4,MATCH(I16,$A$3:$a$20,false)-1,0))),j16>OFFSET($D$3,MATCH(I16,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I16,$A$3:$a$20,false)-1,0))),j16>

=AND(J16>=OFFSET($C$4,MATCH(I16,$A$3:$A$20,FALSE)-1,0),J16<=OFFSET($D$4,MATCH(I16,$A$3:$A$20,FALSE)-1,0))

=AND(J16>=OFFSET($C$5,MATCH(I16,$A$3:$A$20,FALSE)-1,0),J16<=OFFSET($D$5,MATCH(I16,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

E

0.6

=D15

Green

D

0.59

=OR(AND(J17<>0,OR(J17<=OFFSET($C$3,MATCH(I17,$A$3:$A$20,FALSE)-1,0),J17><OFFSET($C$4,MATCH(I17,$A$3:$a$20,false)-1,0))),j17>OFFSET($D$3,MATCH(I17,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I17,$A$3:$a$20,false)-1,0))),j17>

=AND(J17>=OFFSET($C$4,MATCH(I17,$A$3:$A$20,FALSE)-1,0),J17<=OFFSET($D$4,MATCH(I17,$A$3:$A$20,FALSE)-1,0))

=AND(J17>=OFFSET($C$5,MATCH(I17,$A$3:$A$20,FALSE)-1,0),J17<=OFFSET($D$5,MATCH(I17,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

F

KPA %

=C19-5%

1

Red

A

0.92

=OR(AND(J18<>0,OR(J18<=OFFSET($C$3,MATCH(I18,$A$3:$A$20,FALSE)-1,0),J18><OFFSET($C$4,MATCH(I18,$A$3:$a$20,false)-1,0))),j18>OFFSET($D$3,MATCH(I18,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I18,$A$3:$a$20,false)-1,0))),j18>

=AND(J18>=OFFSET($C$4,MATCH(I18,$A$3:$A$20,FALSE)-1,0),J18<=OFFSET($D$4,MATCH(I18,$A$3:$A$20,FALSE)-1,0))

=AND(J18>=OFFSET($C$5,MATCH(I18,$A$3:$A$20,FALSE)-1,0),J18<=OFFSET($D$5,MATCH(I18,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

F

=C20-5%

=C20-0.01%

Amber

B

1

=OR(AND(J19<>0,OR(J19<=OFFSET($C$3,MATCH(I19,$A$3:$A$20,FALSE)-1,0),J19><OFFSET($C$4,MATCH(I19,$A$3:$a$20,false)-1,0))),j19>OFFSET($D$3,MATCH(I19,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I19,$A$3:$a$20,false)-1,0))),j19>

=AND(J19>=OFFSET($C$4,MATCH(I19,$A$3:$A$20,FALSE)-1,0),J19<=OFFSET($D$4,MATCH(I19,$A$3:$A$20,FALSE)-1,0))

=AND(J19>=OFFSET($C$5,MATCH(I19,$A$3:$A$20,FALSE)-1,0),J19<=OFFSET($D$5,MATCH(I19,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

F

0.55

=D18

Green

C

0.61

=OR(AND(J20<>0,OR(J20<=OFFSET($C$3,MATCH(I20,$A$3:$A$20,FALSE)-1,0),J20><OFFSET($C$4,MATCH(I20,$A$3:$a$20,false)-1,0))),j20>OFFSET($D$3,MATCH(I20,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I20,$A$3:$a$20,false)-1,0))),j20>

=AND(J20>=OFFSET($C$4,MATCH(I20,$A$3:$A$20,FALSE)-1,0),J20<=OFFSET($D$4,MATCH(I20,$A$3:$A$20,FALSE)-1,0))

=AND(J20>=OFFSET($C$5,MATCH(I20,$A$3:$A$20,FALSE)-1,0),J20<=OFFSET($D$5,MATCH(I20,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

D

0.95

=OR(AND(J21<>0,OR(J21<=OFFSET($C$3,MATCH(I21,$A$3:$A$20,FALSE)-1,0),J21><OFFSET($C$4,MATCH(I21,$A$3:$a$20,false)-1,0))),j21>OFFSET($D$3,MATCH(I21,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I21,$A$3:$a$20,false)-1,0))),j21>

=AND(J21>=OFFSET($C$4,MATCH(I21,$A$3:$A$20,FALSE)-1,0),J21<=OFFSET($D$4,MATCH(I21,$A$3:$A$20,FALSE)-1,0))

=AND(J21>=OFFSET($C$5,MATCH(I21,$A$3:$A$20,FALSE)-1,0),J21<=OFFSET($D$5,MATCH(I21,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

E

0.61

=OR(AND(J22<>0,OR(J22<=OFFSET($C$3,MATCH(I22,$A$3:$A$20,FALSE)-1,0),J22><OFFSET($C$4,MATCH(I22,$A$3:$a$20,false)-1,0))),j22>OFFSET($D$3,MATCH(I22,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I22,$A$3:$a$20,false)-1,0))),j22>

=AND(J22>=OFFSET($C$4,MATCH(I22,$A$3:$A$20,FALSE)-1,0),J22<=OFFSET($D$4,MATCH(I22,$A$3:$A$20,FALSE)-1,0))

=AND(J22>=OFFSET($C$5,MATCH(I22,$A$3:$A$20,FALSE)-1,0),J22<=OFFSET($D$5,MATCH(I22,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

F

0.95

=OR(AND(J23<>0,OR(J23<=OFFSET($C$3,MATCH(I23,$A$3:$A$20,FALSE)-1,0),J23><OFFSET($C$4,MATCH(I23,$A$3:$a$20,false)-1,0))),j23>OFFSET($D$3,MATCH(I23,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I23,$A$3:$a$20,false)-1,0))),j23>

=AND(J23>=OFFSET($C$4,MATCH(I23,$A$3:$A$20,FALSE)-1,0),J23<=OFFSET($D$4,MATCH(I23,$A$3:$A$20,FALSE)-1,0))

=AND(J23>=OFFSET($C$5,MATCH(I23,$A$3:$A$20,FALSE)-1,0),J23<=OFFSET($D$5,MATCH(I23,$A$3:$A$20,FALSE)-1,0))

FALSE

FALSE

TRUE

E

0.56

=OR(AND(J24<>0,OR(J24<=OFFSET($C$3,MATCH(I24,$A$3:$A$20,FALSE)-1,0),J24><OFFSET($C$4,MATCH(I24,$A$3:$a$20,false)-1,0))),j24>OFFSET($D$3,MATCH(I24,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I24,$A$3:$a$20,false)-1,0))),j24>

=AND(J24>=OFFSET($C$4,MATCH(I24,$A$3:$A$20,FALSE)-1,0),J24<=OFFSET($D$4,MATCH(I24,$A$3:$A$20,FALSE)-1,0))

=AND(J24>=OFFSET($C$5,MATCH(I24,$A$3:$A$20,FALSE)-1,0),J24<=OFFSET($D$5,MATCH(I24,$A$3:$A$20,FALSE)-1,0))

FALSE

TRUE

FALSE

F

0.53

=OR(AND(J25<>0,OR(J25<=OFFSET($C$3,MATCH(I25,$A$3:$A$20,FALSE)-1,0),J25><OFFSET($C$4,MATCH(I25,$A$3:$a$20,false)-1,0))),j25>OFFSET($D$3,MATCH(I25,$A$3:$A$20,FALSE)-1,0))</OFFSET($C$4,MATCH(I25,$A$3:$a$20,false)-1,0))),j25>

=AND(J25>=OFFSET($C$4,MATCH(I25,$A$3:$A$20,FALSE)-1,0),J25<=OFFSET($D$4,MATCH(I25,$A$3:$A$20,FALSE)-1,0))

=AND(J25>=OFFSET($C$5,MATCH(I25,$A$3:$A$20,FALSE)-1,0),J25<=OFFSET($D$5,MATCH(I25,$A$3:$A$20,FALSE)-1,0))

<tbody>
</tbody>

 

[LateralThinker]

Thanks jorismoerings.

 

[Biz]

Thank You LateralThinker and jorismoerings for the very nice solutions.

@ jorismoerings missing items in red text

Red Formula should be
=AND($J2<>0,OR($J2<=SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Red")*($C$3:$C$20)),$J2>SUMPRODUCT(($A$3:$A$20=$I2)*($E$3:$E$20="Red")*($D$3:$D$20))))

Thank you again guys.

Biz