Assign number to 11 different texts

[nicolehalliday]

Hi,
I am trying to assign a different number to 11 separate variations of text within a range. To be specific, from D4:D103 there are eleven different text which are Moodys credit ratings (Ba1, Ba2, Ba2, B1, B2, B3, Caa1, Caa2, Caa3, Ca and C). From E4:E103 I would like to assign a number from 1-11 respectively. I tried to do this by embedding if statements into the cell but the maximum I am allowed is 8. I have also tried some formulas I found in other threads that required CTRL+SHIFT+ENTER, but none of those seemed to work for me. I am no Excel whiz so I would really appreciate some help! Thanks

 

[VoG]

Hi. Try creating a lookup table then use VLOOKUP http://www.contextures.com/xlFunctions02.html

 

[nicolehalliday]

Thanks for the reply. I still don't really understand how I can use vlookup to return a number, based on 11 different conditions (like I said, I am not experienced in Excel). Wouldn't there be 11 different lookup values? D4:D103 contains 11 different ratings that vary from Ba1 to C in no order or proper distribution. Could you give me some advice as to how I'd set up my Vlookup, or if there are other options?

Thanks

 

[VoG]

See this small example

Excel Workbook
	D	E	F	G	H	I
1					BA1	1
2					Ba2	2
3					Ba3	3
4	Ba3	3			Caa1	4
Sheet2