Help with writing an "IF" Statement

[AChampagne]

Hello, I need help writing an "IF" statement.

Cell C2 contains one of five possible responses: Exceptional, Highly Effective, Solid Performance, Needs Improvement, Not Effective

In cell C3, I would like to assign a numerical answer based on C2. For example: If C2 equals Exceptional I would like the number 5 to appear in cell C3. If C2 equals Highly Effective I would like the number 4 to appear in cell C3 and so on.

Help??

 

[jtakw]

Hi,

You can have the possible responses hard coded in the formula like in C3, or you can enter the possible responses in a column or row of cells (i.e. E1:E5) and reference it in the formula like in C6:

Excel 2010
	C	D	E
1			Exceptional
2	Highly Effective		Highly Effective
3	4		Solid Performance
4			Needs Improvement
5	Solid Performance		Not Effective
6	3
Sheet2

Cell Formulas
Range	Formula
C3	=LOOKUP(C2,{"Exceptional","Highly Effective","Solid Performance","Needs Improvement","Not Effective"},{5,4,3,2,1})
C6	=LOOKUP(C5,E1:E5,{5,4,3,2,1})

 

[Jonmo1]

You have the right idea with lookup, but the text array needs to be sorted in ascending order.

I might have chosen match instead..

=MATCH(C2,{"Not Effective","Needs Improvement","Solid Performance","Highly Effective","Exceptional"},0)