Number range function

#1 07-18-2005, 08:05 PM

Is there a function in excel where you can look up a number range and return
a value, rather than having to type out the full number range in a standard
vlookup function. For example, for vehicle numbers 1 - 50 I want to return a
type description "Car". I will have approx 60 different number ranges e.g.
1-50, 51-100, 101-150..etc.etc.

Bryan Hessey · #2 07-18-2005, 08:19 PM

Lets hope for a better solution, but in case:

enter 1, in A1 and hold the CTRLK key and formula drag to 600, this will number the range for you.
enter Car in B1 and drag to 50, Truck in 51 and drag to 100 etc.
The range can then be used as a range, or named and used as a Named Range.

(note, to Formula drag, click the small square in the bottom right corner of the highlight)

#3 07-18-2005, 09:05 PM

I'd create a list like this in sheet2!a1:b##:

1 car
51 truck
101 Van
151 Firetruck
201 policecar
....

Then use:
=vlookup(a1,sheet2!a:b,2)

Note that there isn't a False or 0 as the last parm in the function.

Sonya T wrote:
>
> Is there a function in excel where you can look up a number range and return
> a value, rather than having to type out the full number range in a standard
> vlookup function. For example, for vehicle numbers 1 - 50 I want to return a
> type description "Car". I will have approx 60 different number ranges e.g.
> 1-50, 51-100, 101-150..etc.etc.

--

Dave Peterson

Welcome to the Excel Forum

Forum Statistics