I am trying to create a function similar to VLOOKUP, but different. I have a
range of numbers, and I need the function to look at the starting number and
see if it is between, for example, 0 and 50 dollars, and if it is, return one
value. If it is between 50 and 100, another, etc. VLOOKUP doesn't do between,
just exact. What I am looking for is like what you do on your income tax
form, where you look for your wages between certain numbers. Does anyone
have a suggestion?

One simple way if not too many is to nest ifs starting the the highest.
=if(a1>100,1,if(a1>50,2,"no"))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Kim M." <Kim M.@discussions.microsoft.com> wrote in message
news:3C8A1503-A2F4-47D5-9D47-(E-Mail Removed)...
>I am trying to create a function similar to VLOOKUP, but different. I have
>a
> range of numbers, and I need the function to look at the starting number
> and
> see if it is between, for example, 0 and 50 dollars, and if it is, return
> one
> value. If it is between 50 and 100, another, etc. VLOOKUP doesn't do
> between,
> just exact. What I am looking for is like what you do on your income tax
> form, where you look for your wages between certain numbers. Does anyone
> have a suggestion?

>I am trying to create a function similar to VLOOKUP, but different. I have
>a
> range of numbers, and I need the function to look at the starting number
> and
> see if it is between, for example, 0 and 50 dollars, and if it is, return
> one
> value. If it is between 50 and 100, another, etc. VLOOKUP doesn't do
> between,
> just exact. What I am looking for is like what you do on your income tax
> form, where you look for your wages between certain numbers. Does anyone
> have a suggestion?

If your intervals are all the same, you can divide by the interval amount,
add one to it, and use it as the index value in a CHOOSE function call.
Using your 50 unit interval example from your posting...

=CHOOSE(1+A1/50,Value1,Value2,Value3,Value4,Value5,<<etc.>>)

Rick