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
[TABLE="width: 175"]
<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>[TR]
[TD="class: xl69, width: 234, bgcolor: transparent, colspan: 2"]Wage Earners[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Evaluation Mark[/TD]
[TD="class: xl67, bgcolor: transparent"]Bonus Amount[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, bgcolor: transparent"] $ - [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 10.00 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 20.00 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 30.00 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 40.00 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, colspan: 2"]Salary $0 to $54,999.00[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Evaluation Mark[/TD]
[TD="class: xl67, bgcolor: transparent"]Bonus Amount[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, bgcolor: transparent"] $ - [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 10.00 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 20.00 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 30.00 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 40.00 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, colspan: 2"]Salary $55,000.00 to $74,999.00[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Evaluation Mark[/TD]
[TD="class: xl67, bgcolor: transparent"]Bonus Amount[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, bgcolor: transparent"] $ - [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 11.00 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 21.00 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 31.00 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 41.00 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, colspan: 2"]Salary $75,000.00 to $104,999.00[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Evaluation Mark[/TD]
[TD="class: xl67, bgcolor: transparent"]Bonus Amount[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl68, bgcolor: transparent"] $ - [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 12.00 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]10[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 22.00 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]15[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 33.00 [/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]20[/TD]
[TD="class: xl68, bgcolor: transparent"] $ 44.00 [/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 441"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Wage or Salary[/TD]
[TD]Salary[/TD]
[TD]Evaluation Mark[/TD]
[TD] Bonus[/TD]
[TD]

[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]W[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]11[/TD]
[TD] ???[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]S[/TD]
[TD="align: right"]56000[/TD]
[TD="align: right"]22[/TD]
[TD] ???[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]W[/TD]
[TD="align: right"]56000[/TD]
[TD="align: right"]3[/TD]
[TD] ???[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Susan[/TD]
[TD]S[/TD]
[TD="align: right"]79000[/TD]
[TD="align: right"]16[/TD]
[TD] ???[/TD]
[TD]

[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="2"></colgroup>[/TABLE]

Problem

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

 

[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)))))