• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Alocate value in a table of "up to" values and get number next to it

C

CarlosV

New Member
Hi all,

I need excel to know in what range a value falls and get bac a fee thats asing to that range.

Normaly I will use Vlookup but the ranges are many ( for example, up to 95k the next one is up to 100k but the next is up to 101.5k).

I already have my ranges in one column and each fee next to its range in another column.

Apreciate any and all comments.

C.V.
 
If you build a data table like this:
Code: 
MinValue       Name
0             Smallest group
95,000        Next biggest
100,000       Getting bigger!
101,500       Premium club

You could then write a simply LOOKUP function like:
=LOOKUP(96000,A1:B5)
which, in this case, would return "Next biggest!"
 
Luke M said:
If you build a data table like this:
Code: 
MinValue       Name
0             Smallest group
95,000        Next biggest
100,000       Getting bigger!
101,500       Premium club

You could then write a simply LOOKUP function like:
=LOOKUP(96000,A1:B5)
which, in this case, would return "Next biggest!"
Click to expand...

Thanks Luke, great example, using this same example I would need excel to return "Getting bigger" because my ranges are max values or "up to" values. That is my problem right now, everything I try, gets me the below range
 
Change the table then to show the max of the previous group. So, if the "Getting bigger" is everything up to and including 95k, table would be:
Code: 
MinValue       Name
0             Getting bigger!
95,001        This is everything between 95k and 100k
100,001       This is everything between 100k and 101.5k
101,501       This is everything that is above that
 
Luke M said:
Change the table then to show the max of the previous group. So, if the "Getting bigger" is everything up to and including 95k, table would be:
Code: 
MinValue       Name
0             Getting bigger!
95,001        This is everything between 95k and 100k
100,001       This is everything between 100k and 101.5k
101,501       This is everything that is above that
Click to expand...

The force is strong with you Luke!!
 
You must log in or register to reply here.
Back
Top