First, get % of Quarter 1: current/Quota: I3/INDEX($B$3:$E$3,MATCH(I2,$B$2:$E$2,0))
Below formulas are getting Factor in J3 then copy to M,P,S column:
With IF:
=IF((I3/INDEX($B$3:$E$3,MATCH(I2,$B$2:$E$2,0)))>=1.5,10,IF((I3/INDEX($B$3:$E$3,MATCH(I2,$B$2:$E$2,0)))>=1.25,8.5,IF((I3/INDEX($B$3:$E$3,MATCH(I2,$B$2:$E$2,0)))>=1,6,IF((I3/INDEX($B$3:$E$3,MATCH(I2,$B$2:$E$2,0)))>0.75,4,IF((I3/INDEX($B$3:$E$3,MATCH(I2,$B$2:$E$2,0)))>0.5,2,0)))))
Or with LOOKUP much shorter:
=LOOKUP(I3/INDEX($B$3:$E$3,MATCH(I2,$B$2:$E$2,0)),{0,0.5,0.75,1,1.25,1.5},{0,2,4,6,8.5,10})
Bookmarks