IF Statement Help! Tiered Question

[UrbanJungleFashion]

A B C D E F

[TABLE="width: 384"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Goal[/TD]
[TD]Stretch Goal 1 (5)[/TD]
[TD]Stretch Goal 2 (10)[/TD]
[TD]Stretch Goal 3 (15)[/TD]
[TD]Actual[/TD]
[/TR]
[TR]
[TD]Store 1[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Store 2[/TD]
[TD]29[/TD]
[TD]31[/TD]
[TD]32[/TD]
[TD]33[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]Store 3[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]16
[/TD]
[/TR]
</tbody>[/TABLE]


The End Result Needed:

If the store reaches it's stretch goal 1 they qualify for 5 points. If they meet strectch goal 2 they get 10 points. If they meet stretch goal three then they get 15 points.

When I put this formula =IF(F1<C1,5,IF(F1<D1,10,IF(F1<E1,15,0)))

When i put this formula in I keep getting zero.

Will someone PLEASE help me with this?

Thank You!

 

[bbott]

Try:

=LOOKUP(F2,C2:E2,{5,10,15})

 

[UrbanJungleFashion]

Try:

=LOOKUP(F2,C2:E2,{5,10,15})

Hi, this idd not help me. Is there another formula you can suggest? I tried

=IF(F2<=C2,5,(IF(F2<=D2,10,(IF(F2<=E2,15,0)))))

 

[bbott]

Hi, this idd not help me. Is there another formula you can suggest? I tried

=IF(F2<=C2,5,(IF(F2<=D2,10,(IF(F2<=E2,15,0)))))

Your operators are backwards. Try:

=IF(F2>=E2,15,IF(F2>=D2,10,IF(F2>=C2,5,0)))

But this does exactly the same thing as my original formula....

 

[njimack]

Excel Workbook
	A	B	C	D	E	F	G
1			5	10	15
2		Goal	Stretch Goal 1	Stretch Goal 2	Stretch Goal 3	Actual	Score
3	Store 1	19	20	21	22	25	15
4	Store 2	29	31	32	33	35	15
5	Store 3	8	9	10	11	16	15
Sheet2

 

[UrbanJungleFashion]

Sheet2

*	A	B	C	D	E	F	G
*	*	*	*
*	Goal	Stretch Goal 1	Stretch Goal 2	Stretch Goal 3	Actual	Score
Store 1
Store 2
Store 3

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]29[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]15[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G3 =IFERROR(INDEX($B$1:$E$1,MATCH(F3,B3:E3,1)),0) G4 =IFERROR(INDEX($B$1:$E$1,MATCH(F4,B4:E4,1)),0) G5 =IFERROR(INDEX($B$1:$E$1,MATCH(F5,B5:E5,1)),0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

THANK YOU SO MUCH!!! That was the perfect solution to my problem!!!!!!!

 

[njimack]

THANK YOU SO MUCH!!! That was the perfect solution to my problem!!!!!!!

You're welcome!