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.

Share This Page