How do I use VLOOKUP to nest more then 7 IF statements?

Discussion in 'Microsoft Excel Misc' started by yoshi, Sep 29, 2008.

  1. yoshi

    yoshi Guest

    Hello,

    I have 12 different values I would like to return based on a range of
    numbers. Right now, I have it set up to display the 7 most common of them
    through nested if statements via this formula:

    =IF(E5>=250,"1.9",IF(E5>=210,"1.8",IF(E5>=170,"1.7",IF(E5>=150,"1.6",IF(E5>=140,"1.5",IF(E5>=130,"1.4",IF(E5>=120,1.2,1)))))))

    How can I include the missing values using a different function? I had read
    to use either an index or lookup function, but don't know how to apply these.

    The missing ranges and values I am trying to add are 0-49:0.70, 50-89:0.90,
    90-109:1.00, 250-299:1.90, >=300:2.00.

    Any assistance would be highly appreciated.

    thanks!
    Yoshi
     
    yoshi, Sep 29, 2008
    #1
    1. Advertisements

  2. yoshi

    muddan madhu Guest

    try this

    =IF(E5>=250,"1.9",IF(E5>=210,"1.8",IF(E5>=170,"1.7",IF(E5>=150,"1..6",IF(E5>=140,"1.5",IF(E5>=130,"1.4",IF(E5>=120,1.2,1)))))))

    define the above formula, go to insert | names | define | refers to
    cell where u have the formula | name it as formula1 | ok

    similary define for other forumlas... then

    use
    =if(formula1,formula1,if(formula2,formula2,if(........................)))



    On Sep 30, 1:10 am, yoshi <> wrote:
    > Hello,
    >
    > I have 12 different values I would like to return based on a range of
    > numbers. Right now, I have it set up to display the 7 most common of them
    > through nested if statements via this formula:
    >
    > =IF(E5>=250,"1.9",IF(E5>=210,"1.8",IF(E5>=170,"1.7",IF(E5>=150,"1.6",IF(E5>=140,"1.5",IF(E5>=130,"1.4",IF(E5>=120,1.2,1)))))))
    >
    > How can I include the missing values using a different function? I had read
    > to use either an index or lookup function, but don't know how to apply these.
    >
    > The missing ranges and values I am trying to add are 0-49:0.70, 50-89:0.90,
    > 90-109:1.00, 250-299:1.90, >=300:2.00.
    >
    > Any assistance would be highly appreciated.
    >
    > thanks!
    > Yoshi
     
    muddan madhu, Sep 29, 2008
    #2
    1. Advertisements

  3. yoshi

    Sheeloo Guest

    You can use VLOOKUP with last parameter as TRUE...

    Set up your ranges (in ascending order) and corresponding value to get in
    two adjacent columns
    COL H COL I
    0 0.7
    49 0.9
    89 1
    109 1.9
    300 2

    and use VLOOKUP with TRUE in B1 and copy down ...
    =VLOOKUP(A1,H:I,2,TRUE)

    A1 should have values 0-300+
    You will get the following result
    0 0.7
    23 0.7
    49 0.9
    88 0.9
    89 1
    108 1
    109 1.9
    110 1.9
    298 1.9
    299 1.9
    300 2
    330 2


    "muddan madhu" wrote:

    > try this
    >
    > =IF(E5>=250,"1.9",IF(E5>=210,"1.8",IF(E5>=170,"1.7",IF(E5>=150,"1..6",IF(E5>=140,"1.5",IF(E5>=130,"1.4",IF(E5>=120,1.2,1)))))))
    >
    > define the above formula, go to insert | names | define | refers to
    > cell where u have the formula | name it as formula1 | ok
    >
    > similary define for other forumlas... then
    >
    > use
    > =if(formula1,formula1,if(formula2,formula2,if(........................)))
    >
    >
    >
    > On Sep 30, 1:10 am, yoshi <> wrote:
    > > Hello,
    > >
    > > I have 12 different values I would like to return based on a range of
    > > numbers. Right now, I have it set up to display the 7 most common of them
    > > through nested if statements via this formula:
    > >
    > > =IF(E5>=250,"1.9",IF(E5>=210,"1.8",IF(E5>=170,"1.7",IF(E5>=150,"1.6",IF(E5>=140,"1.5",IF(E5>=130,"1.4",IF(E5>=120,1.2,1)))))))
    > >
    > > How can I include the missing values using a different function? I had read
    > > to use either an index or lookup function, but don't know how to apply these.
    > >
    > > The missing ranges and values I am trying to add are 0-49:0.70, 50-89:0.90,
    > > 90-109:1.00, 250-299:1.90, >=300:2.00.
    > >
    > > Any assistance would be highly appreciated.
    > >
    > > thanks!
    > > Yoshi

    >
    >
     
    Sheeloo, Sep 29, 2008
    #3
  4. Index / match tends to work better...
    In A1:B11 add
    0 0.7
    50 0.9
    90 1
    110 1.2
    130 1.4
    140 1.5
    150 1.6
    170 1.7
    210 1.8
    250 1.9
    300 2

    Now use the formula (In cell E1 or ???)
    =INDEX($B$1:$B$11, MATCH(D1, $A$1:$A$11, TRUE))

    Where you put the amount in Cell D1
    --
    HTH...

    Jim Thomlinson


    "yoshi" wrote:

    > Hello,
    >
    > I have 12 different values I would like to return based on a range of
    > numbers. Right now, I have it set up to display the 7 most common of them
    > through nested if statements via this formula:
    >
    > =IF(E5>=250,"1.9",IF(E5>=210,"1.8",IF(E5>=170,"1.7",IF(E5>=150,"1.6",IF(E5>=140,"1.5",IF(E5>=130,"1.4",IF(E5>=120,1.2,1)))))))
    >
    > How can I include the missing values using a different function? I had read
    > to use either an index or lookup function, but don't know how to apply these.
    >
    > The missing ranges and values I am trying to add are 0-49:0.70, 50-89:0.90,
    > 90-109:1.00, 250-299:1.90, >=300:2.00.
    >
    > Any assistance would be highly appreciated.
    >
    > thanks!
    > Yoshi
     
    Jim Thomlinson, Sep 29, 2008
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. jayhawk1919

    Anyway to nest more than 7 "IF"s?

    jayhawk1919, Oct 28, 2003, in forum: Microsoft Excel Misc
    Replies:
    19
    Views:
    271
    rharmelink
    Oct 31, 2003
  2. Guest
    Replies:
    3
    Views:
    150
    Damon Longworth
    Jul 22, 2005
  3. Guest

    Is it possible to nest more than 7 functions

    Guest, Apr 3, 2007, in forum: Microsoft Excel Misc
    Replies:
    8
    Views:
    148
    Bob I
    Apr 18, 2007
  4. Guest

    Can you combine VLOOKUP with a nest HLOOKUP?

    Guest, Jul 3, 2007, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    17,308
    Guest
    Jul 3, 2007
  5. KenCanuck

    How do I nest more then 7 IF's?

    KenCanuck, Jul 17, 2008, in forum: Microsoft Excel Misc
    Replies:
    13
    Views:
    601
    Roger Govier
    Mar 9, 2010
Loading...

Share This Page