Returning a value based on 3 variables, one of which requires looking between a range

Hi Guys,

First time posting - usually i find the answers to all my excel queries on here from previous posts, but this one seems to be evading me!

I've got a complicated Agent fee structure agreement which I've inherited. At the minute the team work through and manually check that each fee entered manually by the sales guys is in line with the agreed fee structure of each agent. Hundreds of agents each with their own structure

Agent From To Type Fee
Agent 1 1 115000 buying £375
Agent 1 115001 200000 buying £405
Agent 1 200001 350000 buying £455
Agent 1 350001 500000 buying £505
Agent 1 500001 750000 buying £705
Agent 1 750000 9999999 buying £705
Agent 1 1 115000 selling £375
Agent 1 115001 200000 selling £405
Agent 1 200001 350000 selling £455
Agent 1 350001 500000 selling £505
Agent 1 500001 750000 selling £705


Agent 1's fee structure is above as an example. so if the price is between £115,001 and £200,000, they are buying and they are agent 1 the fee should be 405.

I usually make a unique reference field i.e. Agent1&buying&Price and then vlookup into the fee structure, but because the price is between various different range values i'm struggling to automate.

Any help greatly appreciated!!

Eoghan

So long as there are no overlaps in the From/To price ranges, you could put all 4 criteria (Agent, Type, <=Price, >=Price) in a SUMIFS function.