# specifying a range of numbers (i know this should be easy)

#### tonywhite

##### New Member
I need a little "beginner-level" assistance. I'm ashamed that I can't find such things in the excel help files for this, but I thought I'd ask the forums -- a wonderful resource for help, btw.

What I want to do: list a value in an excel cell that is specified as a range. Such as: values 1 through 10.

What I'm using it for:
Currently I'm using a vlookup table (another suggestion from this forum that has worked great!) to return a value. Here's kind of the breakdown for what I'd like to accomplish:
If A1 is 0 - 1.5, return the value "red".
If A1 is 1.6 - 5.0, return the value "blue".
If A1 is 5.1 - 15.0, return the value "green".
and on and on.

Currently, I am having to use "ceiling" to take a decimal place, round it up to nearest ".5", then going to the lookup table to return the right value. The formula I'm using is =VLOOKUP((CEILING(D7,.5)),\$A\$1:\$B\$20,2,True)

and my current "vlookup" table looks something like this:
A B
0.5 red
1.0 red
1.5 red
2.0 blue
2.5 blue
3.0 blue
etc.

I would much rather have it be
A B
0-1.5 red
1.6-5.0 blue
5.1-15.0 green
etc.

only, I don't know how to specify the "range" part.

Anybody know how I can tell excel "zero through 1.5" ?

Thanks!
Tony

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### Corticus

##### Well-known Member
If you set up your lookup table like this:

A B
0 red
1.5 red
1.6 green
6 green
6.1 blue
10 blue
10.1 orange
100 orange

its not exactly what you asked for, but if you used a formula like this:

=VLOOKUP(ROUNDUP(D1,1),\$A\$1:\$B\$8,2,TRUE)

assuming data your f(x)ing is populating D1:D8, f(x) would be in column E.

(I'm on a friends computer, and oh I miss Colo's wonderful tool!)

You have effectively said if D1 is between 0 and 1.5 put red, if its greater than 1.5 but less than or equal to 6 put green, etc.

Maybe this helps?

Corticus
This message was edited by Corticus on 2002-09-18 17:42

Replies
4
Views
629
Replies
3
Views
1K
Replies
1
Views
706
Replies
7
Views
286
Replies
5
Views
381

1,181,582
Messages
5,930,734
Members
436,757
Latest member
Marijn

### 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.

### Which adblocker are you using?

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

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