Creating a formula to select a value in a table based on a criteria

[davidnyke]

Hi All

I have tried using if statements and the like to build a formula but I'm a bit out of my depth. I will paste an example below and explain what I want to achieve. If anybody could help I would be so grateful

Turnover: £600,000.00
Net Profit £65,000.00
Net Profit Margin 10.83%

Example.png

I want to be able to enter our turnover and net profit and calculate the bonus pot based on the turnover and net profit percentage. For example if turnover was £600,000 and Net Profit was £65,000 it would give a net profit margin of 10.83% (so turnover is greater than 500000 but less than 750000 and net profit margin is greater than 10 but less than 12.5) then it would select 4% from the correct cell and calculate the bonus pot as 4% of net profit (4% of 65,000 = £2600).

Hope that makes sense!!

Thank you

[Special-K]

Can you post a spreadsheet?

[davidnyke]

SAMPLE.xlsx

Hi

Thanks for your reply

I have attached a sample

I hope it makes sense but not sure if it would be better laid out another way. Thanks for any help you can provide.

[Special-K]

Try

in B17
=OFFSET($B$9,MATCH(C4,B9:B13,1),(MATCH(C2,B7:I7,1)-1)/2*2+1)

Check the results, I only checked a few of them.

[davidnyke]

Thanks very, that is brilliant. I'm going to try and learn how to do that now. Only thing that I couldn't get to work is when profit is greater than 15% it just returns 0%. Thank you so much though, I am one step closer

[davidnyke]

Really sorry to be a pain Special-K, i've found quite a few of the results aren't reading correct, I think I understand the formula in that it is trying to match the cells with the different ranges but can't get it to work, I'm also not so sure on the end bit of the formula and what it does, the /2*2+1 bit. Thanks again

[samba_ravi]

see the attached file
i make little changes to the workbook