IF Statement Help! Tiered Question

[UrbanJungleFashion]

A B C D E F

	Goal	Stretch Goal 1 (5)	Stretch Goal 2 (10)	Stretch Goal 3 (15)	Actual
Store 1	19	20	21	22	25
Store 2	29	31	32	33	35
Store 3	8	9	10	11	16

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

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

<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>
</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!