Need help with formula in an IF statement please

[Bill Williams]

I am having trouble getting my formulae to work in If statements and would very much appreciate some help

I would have attached a file with a description of issues but not allowed so done my best below.

Thank you kindly

Bill

Sheet 1

Wage Earners
Evaluation Mark	Bonus Amount
0	$ -
3	$ 10.00
10	$ 20.00
15	$ 30.00
20	$ 40.00
Salary $0 to $54,999.00
Evaluation Mark	Bonus Amount
0	$ -
3	$ 10.00
10	$ 20.00
15	$ 30.00
20	$ 40.00
Salary $55,000.00 to $74,999.00
Evaluation Mark	Bonus Amount
0	$ -
3	$ 11.00
10	$ 21.00
15	$ 31.00
20	$ 41.00
Salary $75,000.00 to $104,999.00
Evaluation Mark	Bonus Amount
0	$ -
3	$ 12.00
10	$ 22.00
15	$ 33.00
20	$ 44.00

<colgroup><col style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;" width="107"> <col style="width: 95pt; mso-width-source: userset; mso-width-alt: 4644;" width="127"> <tbody> </tbody>

Sheet 2

Name	Wage or Salary	Salary	Evaluation Mark	Bonus
Steve	W	50000	11	???
Fred	S	56000	22	???
George	W	56000	3	???
Susan	S	79000	16	???

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

Problem

Needed
??? (from sheet 2) = Bonus which is decided based on the following criteria
Is 'Wage or Salary' = W or S from sheet 2
If W
Bonus is based on evaluation mark on wage Earners Table
IE for Steve = W so go to Wage Earners table. Evaluation for Steve from sheet 2 = 11 which corresponds 10 on Wage Eaqrners table
Therefore bonus = 20
If S then table needs to be selected from one of the 3 other (salary) tables 1st
Then same as above fo find bonus
IE Susan Salary (79,000) / Table = Salary $75….
Evaluation = 16
Therefore bonus = 33

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

 

[dermie_72]

You could try:

=IF(B2="W",INDEX(Sheet1!$B$3:$B$7,MATCH(D2,Sheet1!$A$3:$A$7,1)),IF(C2<55000,INDEX(Sheet1!$B$10:$B$14,MATCH(D2,Sheet1!$A$10:$A$14,1)),IF(C2<75000,INDEX(Sheet1!$B$18:$B$22,MATCH(D2,Sheet1!$A$18:$A$22,1)),INDEX(Sheet1!$B$26:$B$30,MATCH(D2,Sheet1!$A$26:$A$30,1)))))