Multiple criteria lookup if statement!

[Buttercup]

Each month we import a list of product sales which shows individual purchases made. This can come in any order and show varying amount’s paid.

In basic terms I need something to:-
1. look at the product type in column A
2. look at the amount paid by the customer in column B
3. compare it to the products pricing table and return the relevant value taking into account any over / under payments. (Some people overpay for a product so it needs to identify that they haven’t paid enough for the higher category so to group it as the category below).

What complicates things products have different price bands. I originally cobbled together an if statement that did the job but then more price bands were introduced making too many prices for it to work properly. I have considered having a criteria table and using dcounta but there are hundreds of products and several different pricing bands so the potential combinations are huge.

I've attached an example of what I mean (we're not fruit product based but was easier to do it with this!) Can anyone point me in the right direction?