if formula

[rizexcel]

=IF(K21="A",4,IF(K21="A-",3.7,IF(K21="B+",3.3,IF(K21="B",3,IF(K21="B-",2.7,IF(K21="C+",2.3,IF(K21="C",2,IF(K21="C-",1.7,0))))))))

above formula accepts 8 conditions. If more than 8 required then what will be the solution. Please help

 

[dave3009]

Hi

Couple of options, first to make a lookup table and use VLOOKUP. If you have no space for the table you can use LOOKUP.

Excel 2007
	A	B	C	D	E	F
3	A	4			A	4
4	A-	3.7			A	4
5	B+	3.3
6	B	3
7	b-	2.7
8	C+	2.3
9	C	2
10	C-	1.7
Sheet1

Cell Formulas
Range	Formula
F3	=VLOOKUP(E3, $A$3:$B$10, 2, 0)
F4	=LOOKUP(E4, {"A","A-","B+","B","B-","C+","C","C-"}, {4,3.7,3.3,3,2.7,2.3,2,1.7})

 

[kevatarvind]

Duplicate Post Same Question Again


http://www.mrexcel.com/forum/excel-questions/698044-if-statement.html

 

[rizexcel]

if you see there is some difference.

 

[kevatarvind]

if you see there is some difference.

you just changed the crietaria

 

[rizexcel]

=LOOKUP(E4, {"A","A-","B+","B","B-","C+","C","C-"}, {4,3.7,3.3,3,2.7,2.3,2,1.7})

"B" and "A-" return same value i.e. 3.7
"C-" and "C+" return same value i.e. 1.7

 

[dave3009]

You could try a single VLOOKUP in that case.

=VLOOKUP(F4, {"A",4;"A-",3.7;"B+",3.3;"B",3;"b-",2.7;"C+",2.3;"C",2;"C-",1.7}, 2, 0)