Range formula

L

lord_legrand

Board Regular
Joined
Aug 21, 2010
Messages
54
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!!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
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 />
 
Upvote 0
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
ABCDEFG
10@#N/A#N/A1A
21AAA101B
32AAA201C
4100AAA301D
5101BBB401E
61000JJJ501F
71001KN/AJ601G
8701H
9801I
10901J
Sheet1
Cell Formulas
RangeFormula
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Create totals using drag down
Replies
2
Views
302
Danny54
D
T
  • Question Question
What if investment
Replies
2
Views
66
tigerzen
T
J
  • Solved
Sumifs - Using Multiple Criteria and Multiple Values
Replies
7
Views
344
NickFru
N
T
  • Question Question
Query two values to return a value
Replies
3
Views
314
TRobert
T
M
  • Question Question
valid data input
Replies
5
Views
292
mwvirk
M

Forum statistics

Threads
1,223,919
Messages
6,175,368
Members
452,638
Latest member
Oluwabukunmi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top