I have a range of numbers which I need to compare against a table to determine a scaled output. So, for example the table has 7 different options, less than 1000 (equals 1), between 1001-2000 (equals 2), between 2001-3000 (equals 3), etc and they relate to different rates for certain products.

When we input numbers, for example 2431, the output would automatically determine the first 1000 is to be multiplied by rate 1 for that product, then 1001-2000 is to be multiplied by rate 2 for that product and the final 431 is to be multiplied by rate 3 for that product.

I know its a lookup but I dont know how to do a range lookup for a scaled outcome like this. I could just do a huge IF formula but I know there is a better way.

I have attached the spreadsheet im working so it is clearer and perhaps the solution can just be put directly into the spreadsheet.

Thanks

Veolia-Pricing---TROY.xlsx