Need Help! Formula with too many conditions!

[AnaMendes85]

Hi.
I work in a store and 2017 started with a new comission system, a new way to calculate how much I'm going to earn with my sales.
I need to have a file where I can put all my sales and that can give me some information.
The file is already done but theres a problema I can't solve.
What I really need is:
In cell D2 I have to choose what kind of category I am: Rookie, Júnior or Expert.
In cell M6 I have the % I already have, based on the sales I already have and my monthly goal.
I need that the O6 cell can tell me how much is worth 1 sales point based on the table under.
I tried to do it with just one category and I did it with a very long if function. But I just can't put 3 conditions I want in just one formula: my category, how much I already have in % and the value per point.

Hope you can help me!
Don't mind my english, I'm portuguese.

Thank you foryour attention.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]<70%[/TD]
[TD]70%-80%[/TD]
[TD]80%-90%[/TD]
[TD]90%-100%[/TD]
[TD]100%-110%[/TD]
[TD]110%120%[/TD]
[TD]120%-130%[/TD]
[TD]130%-140%[/TD]
[TD]140%-150%[/TD]
[TD]>150%[/TD]
[/TR]
[TR]
[TD]Rookie[/TD]
[TD]0€[/TD]
[TD]2.28€[/TD]
[TD]4.55€[/TD]
[TD]5.20€[/TD]
[TD]6.50€[/TD]
[TD]7.15€[/TD]
[TD]7.80€[/TD]
[TD]8.45€[/TD]
[TD]9.10€[/TD]
[TD]9.75€[/TD]
[/TR]
[TR]
[TD]Junior[/TD]
[TD]0€[/TD]
[TD]2.45€[/TD]
[TD]4.90€[/TD]
[TD]5.60€[/TD]
[TD]7.00€[/TD]
[TD]7.70€[/TD]
[TD]8.40€[/TD]
[TD]9.10€[/TD]
[TD]9.80€[/TD]
[TD]10.50€[/TD]
[/TR]
[TR]
[TD]Expert[/TD]
[TD]0€[/TD]
[TD]2.63€[/TD]
[TD]5.25€[/TD]
[TD]6.00€[/TD]
[TD]7.50€[/TD]
[TD]8.25€[/TD]
[TD]9.00€[/TD]
[TD]9.75€[/TD]
[TD]10.50€[/TD]
[TD]11.25€[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

HI, welcome to the board

1. you have a big mistake in your headings...
for instance, if you have 80%, would you use 70-80 or 80-90?

80-90 (for instance) is not a numeric value, it is text, you need to use a single value. Then you can use INDEX/MATCH to find what you want...

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td]70%[/td][td]80%[/td][td]90%[/td][td]100%[/td][td]110%[/td][td]120%[/td][td]130%[/td][td]140%[/td][td]150%[/td][td]500%[/td][/tr]

[tr][td]

2​

[/td][td]Rookie[/td][td]0 €[/td][td]2.28 €[/td][td]4.55 €[/td][td]5.20 €[/td][td]6.50 €[/td][td]7.15 €[/td][td]7.80 €[/td][td]8.45 €[/td][td]9.10 €[/td][td]9.75 €[/td][/tr]

[tr][td]

3​

[/td][td]Junior[/td][td]0 €[/td][td]2.45 €[/td][td]4.90 €[/td][td]5.60 €[/td][td]7.00 €[/td][td]7.70 €[/td][td]8.40 €[/td][td]9.10 €[/td][td]9.80 €[/td][td]10.50 €[/td][/tr]

[tr][td]

4​

[/td][td]Expert[/td][td]0 €[/td][td]2.63 €[/td][td]5.25 €[/td][td]6.00 €[/td][td]7.50 €[/td][td]8.25 €[/td][td]9.00 €[/td][td]9.75 €[/td][td]10.50 €[/td][td]11.25 €[/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]Rookie[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

100%​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

5.2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
A10=INDEX($B$2:$K$4,MATCH($A$8,A2:A4,0),MATCH($A$9,$B$1:$K$1))

 

[Tetra201]

Try the following:
- place your table in range A1:K4 on a separate sheet named Table;
- change the top row of your table to: Category|0%|70%|80%|90%|100%|110%|120%|130%|140%|150%
- see if this formula works for you:

=HLOOKUP(M6,Table!$B$1:$K$4,MATCH(D2,Table!$A$1:$A$4,0),TRUE)