Dynamic Lookup Depending on Criteria

[teefy]

Hello,

I hope someone can help, as I have brain freeze over this.

I have a worksheet with a series of payout curves:

https://1drv.ms/u/s!AiTfuC9hs0NdnnjZczIuwXQxVYDA

And a second with a summary of sales by person:

https://1drv.ms/u/s!AiTfuC9hs0NdnnmeguWNe1F12q0_

(Sorry, I can't seem to put OneDrive images in the img BBcode.)

Essentially I am wanting to lookup the growth achievements per person against the payout curve for the region they are located in.

I'm using a simple to return the payout value:

=LOOKUP(C2,'Payout Curves'!E:E,'Payout Curves'!F:F)

The problem is the above formula needs to be manually curated for each line, which is not really scaleable.

There must be a way to derive the relevant range in which to LOOKUP from the region information.

My sample spreadsheet can be found here:

https://1drv.ms/x/s!AiTfuC9hs0NdnncxSgE1TY4OesyT

Any pointers on how I might be able to achieve this result with a single formula per column, which can be dropped down without editing, would be highly appreciated.

Many thanks!