Benefit in kind (BIK) Vertical "IF" to match Horizontal data.

[Alex007]

Hi All,

I was wondering if i could get some help. I need a formula to find a certain % rate based on car emissions range. So for example if a car is 115g CO2 it means if diesel its taxed at 25%. See below:


[TABLE="width: 335"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]2016/17
BIK%[/TD]
[TD="colspan: 2"]2017/18
BIK%[/TD]
[/TR]
[TR]
[TD]CO2
g/km[/TD]
[TD]petrol[/TD]
[TD]diesel[/TD]
[TD]petrol[/TD]
[TD]diesel[/TD]
[/TR]
[TR]
[TD]0 (EV)[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]01-50[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]51-75[/TD]
[TD]11[/TD]
[TD]14[/TD]
[TD]13[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]76-94[/TD]
[TD]15[/TD]
[TD]18[/TD]
[TD]17[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]95-99[/TD]
[TD]16[/TD]
[TD]19[/TD]
[TD]18[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]100-104[/TD]
[TD]17[/TD]
[TD]20[/TD]
[TD]19[/TD]
[TD]22[/TD]
[/TR]
[TR]
[TD]105-109[/TD]
[TD]18[/TD]
[TD]21[/TD]
[TD]20[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]110-114[/TD]
[TD]19[/TD]
[TD]22[/TD]
[TD]21[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]115-119[/TD]
[TD]20[/TD]
[TD]23[/TD]
[TD]22[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]120-124[/TD]
[TD]21[/TD]
[TD]24[/TD]
[TD]23[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]125-129[/TD]
[TD]22[/TD]
[TD]25[/TD]
[TD]24[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]130-134[/TD]
[TD]23[/TD]
[TD]26[/TD]
[TD]25[/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]135-139[/TD]
[TD]24[/TD]
[TD]27[/TD]
[TD]26[/TD]
[TD]29[/TD]
[/TR]
[TR]
[TD]140-144[/TD]
[TD]25[/TD]
[TD]28[/TD]
[TD]27[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]145-149[/TD]
[TD]26[/TD]
[TD]29[/TD]
[TD]28[/TD]
[TD]31[/TD]
[/TR]
[TR]
[TD]150-154[/TD]
[TD]27[/TD]
[TD]30[/TD]
[TD]29[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]155-159[/TD]
[TD]28[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]160-164[/TD]
[TD]29[/TD]
[TD]32[/TD]
[TD]31[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]165-169[/TD]
[TD]30[/TD]
[TD]33[/TD]
[TD]32[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]170-174[/TD]
[TD]31[/TD]
[TD]34[/TD]
[TD]33[/TD]
[TD]36[/TD]
[/TR]
</tbody>[/TABLE]

So basically i need a formula to return a % based on emission. All help is welcome.

Thanks

Alex

 

[crazydragon84]

You probably need to separate out the min and max for the range. then you can do a sumifs (sumrange, % > min, % < max). you can through in indirect function in the sumrange if you want to make the column you are looking for is also going to change.