I have a scale, based on which I decide the value of the coefficient for the multiplication. The scale looks as following: 

![Scales](https://i.stack.imgur.com/6NgfZ.png)

Which means that:

* for `Category1`: when `value>=1.000.000` then `coef` is `1`, when `value>=500.000` then `coef` is `0.8` and etc.
* Same logic applies for `Category2`;

Then I have input data in the following format:

```
Company !MainCat|Sales Amount|
Company1|T1     |   6.500.000|
Company2|T2     |      70.000|

```

I need to find corresponding coefficient, ratio of the coeffitient and the value `(=ratio*MaxCoef)`. Currently, I am finding coef the following way:
- for company1:

```
 =IF(C8>=$D2;$D$1;IF(C8>=$E2;$E$1;IF(C8>=$F2;$F$1;IF(C8>=$G2;$G$1;IF(C8>=$H2;$H$1;IF(C8>=$I2;$I$1))))))

```

That is literally hardcoded and doesn't look good. Maybe there is a better way of doing ? Any suggestions?

Formula view:

![Data](https://i.stack.imgur.com/G1nYH.png)