Commission structures with 3 variables, tried multiple formulas

[HR Manager]

I am trying to get commission values to generate based upon three criteria's:

Employee	LPH	QA Score	DH
A	4.6	0.967	37.9
B	3.5	0.951	25.6
C	1.7	0.981	29.2
D	3.4	0.912	37.2
E	7	0.964	15
F	2.1	0.863	20
G	5.5	1	29.5

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

<colgroup><col><col><col></colgroup><tbody>
</tbody>

The commission structure is below:

LPH	QA	DH	Commission
0	0	0	0
4	0.96	34	40
5	0.96	34	80
6	0.97	34	120
7	0.97	36	160
8	0.97	36	200
9	0.98	36	240
10	0.98	36	280
11	1	36	320
12	1	36	360
13	1	36	400

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

How the structure works that the employee must hit the minimum in three column in order to recieve the commission value
For example:
Employee A: would get 40
Employee B: would receive 0
Employee C: would receive 0

I have tried Index, match and vlookup, with if and I am not getting the results I need. Any help would be much appreciated.

 

[JoeMo]

What's the expected result for employees D through G?

 

[HR Manager]

What's the expected result for employees D through G?

D-Would also be 0
E-Would also be 0 because they didn't meet the minimum in DH
F-Would be 0
G-Would be 0 because didn't meet the minimum DH

For Example for a commission receiver would be:

LPH-13
QA-.97
DH-34

They would only receive the minimum $40 because they had the lowest DH score.

Another Example is:
LPH-10
QA-.97
DH-36

They would receive $200, because it is a lower DH than what is expected for 10 LPH.

Hope this helps in order for you to help me, thank for responding

 

[JoeMo]

In the example below col A:D is the data from your post. Col E is the commission (copy the formula in E2 down). Cols G:J contain your "lookup" table. Each column in G:J is a named range whose name is the column header.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Employee	LPH	QA Score	DH	Comm		LPH	QA	DH	Commission
2	A	4.6	0.967	37.9	40		0	0	0	0
3	B	3.5	0.951	25.6	0		4	0.96	34	40
4	C	1.7	0.981	29.2	0		5	0.96	34	80
5	D	3.4	0.912	37.2	0		6	0.97	34	120
6	E	7	0.964	15	0		7	0.97	36	160
7	F	2.1	0.863	20	0		8	0.97	36	200
8	G	5.5	1	29.5	0		9	0.98	36	240
9							10	0.98	36	280
10							11	1	36	320
11							12	1	36	360
12							13	1	36	400
Sheet1

 

[HR Manager]

In the example below col A:D is the data from your post. Col E is the commission (copy the formula in E2 down). Cols G:J contain your "lookup" table. Each column in G:J is a named range whose name is the column header.
Sheet1

	A	B	C	D	E	F	G	H	I	J
1	Employee	LPH	QA Score	DH	Comm		LPH	QA	DH	Commission
2	A	4.6	0.967	37.9	40		0	0	0	0
3	B	3.5	0.951	25.6	0		4	0.96	34	40
4	C	1.7	0.981	29.2	0		5	0.96	34	80
5	D	3.4	0.912	37.2	0		6	0.97	34	120
6	E	7	0.964	15	0		7	0.97	36	160
7	F	2.1	0.863	20	0		8	0.97	36	200
8	G	5.5	1	29.5	0		9	0.98	36	240
9							10	0.98	36	280
10							11	1	36	320
11							12	1	36	360
12							13	1	36	400

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:69px;"><col style="width:30px;"><col style="width:63px;"><col style="width:35px;"><col style="width:64px;"><col style="width:64px;"><col style="width:30px;"><col style="width:35px;"><col style="width:25px;"><col style="width:83px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula E2 =MIN(LOOKUP($B2,LPH,Commission),LOOKUP($C2,QA,Commission),LOOKUP($D2,DH,Commission)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

It worked for most, but there were a few employees it didn't work for and I figured out why.
The report I pull the data from has it going out more decimals than one, 4.98701298701299, the number looks like this in the cell, even though it shows a 5 in the cell, so the formula is still reading it as 4.98 instead of a 5. Is there a way to fix that also? Thank you again!!!

 

[JoeMo]

You are welcome.

The LOOKUP functions require that the lookup vectors (the 2nd argument in the function) are in ascending order. So, sort each of the LPH,QA and DH named ranges so they are in ascending order, otherwise you may get an erroneous result.