Range formula

[lord_legrand]

Happy New year guys,


I hope you can help me with my problem. I need an excel formula that will look at a certain cell and return a value depending where the range of the cell falls into.


The ranges are as follows:

Cell Range Return Value
001-100 A
101-200 B
201-300 C
301-400 D
401-500 E
501-600 F
601-700 G
701-800 H
801-900 I
901-1000 J



Thanks Guys!!

 

[Sandeep Warrier]

Hi

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Range Start</td><td style="font-weight: bold;;">Return Value</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">Number</td><td style="font-weight: bold;;">Return Value</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style=";">A</td><td style="text-align: right;;"></td><td style="text-align: right;;">115</td><td style=";">B</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">101</td><td style=";">B</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">201</td><td style=";">C</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">301</td><td style=";">D</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">401</td><td style=";">E</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">501</td><td style=";">F</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">601</td><td style=";">G</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">701</td><td style=";">H</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">801</td><td style=";">I</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">901</td><td style=";">J</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">D2,$A$2:$B$11,2,TRUE</font>)</td></tr></tbody></table></td></tr></table><br />

 

[taigovinda]

Post 2 is probably the best way, but here's a couple other options that I thought were more fun (each of the three formulas is copied down to show the result, only the green relies on the table off to the right):

Excel Workbook
	A	B	C	D	E	F	G
1	0	@	#N/A	#N/A		1	A
2	1	A	A	A		101	B
3	2	A	A	A		201	C
4	100	A	A	A		301	D
5	101	B	B	B		401	E
6	1000	J	J	J		501	F
7	1001	K	N/A	J		601	G
8						701	H
9						801	I
10						901	J
Sheet1

Cell Formulas
Range	Formula
B1	=CHAR(INT((A1-1)/100)+65)
C1	=LOOKUP(A1,1+100*{0,1,2,3,4,5,6,7,8,9,10},{"A","B","C","D","E","F","G","H","I","J","N/A"})
D1	=VLOOKUP(A1,$F$1:$G$10,2,1)

 

[lord_legrand]

Thanks guys! This is surely helpful!