Using multiple if then functions

[pacerfan07]

Hi, I have the table below and I'm trying to create a formula in column D that satisfies all of these conditions:

• If column B is between 20 and 25 and column C is greater than 30% return a "Y" in column D, otherwise return an "N"
• If column B is between 26 and 30 and column C is greater than 20% return a "Y" in column D, otherwise return an "N"
• If column B is between 31 and 40 and column C is greater than 15% return a "Y" in column D, otherwise return an "N"
• If column B is greater than 41 and column C is greater than 10% return a "Y" in column D, otherwise return an "N"

Basically I want Excel to first look through column B and figure out the tier they belong in, then based on what tier they are in determine if column C meets the criteria described above. I've included what the answers should be in column D. Thanks!

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Avg[/TD]
[TD="align: center"]% Above Avg[/TD]
[TD="align: center"]Y or N[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]John[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]16%[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Chris[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]16%[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Amanda[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]11%[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Sarah[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]60%[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Dave[/TD]
[TD="align: center"]29[/TD]
[TD="align: center"]30%[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]James[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]9%[/TD]
[TD="align: center"]N[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

In D2:

=IFERROR(IF(C2>LOOKUP(B2,{20,26,31,41},{0.3,0.2,0.15,0.1}),"Y","N"),"N")

drag down.

 

[pacerfan07]

Perfect thanks!