Results 1 to 6 of 6

Thread: Nested IFs with VLOOKUP

  1. #1

    Nested IFs with VLOOKUP



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello all Excel professionals, and thank-you in advance for potentially taking the time to help me out here. I am a university student taking an Excel course and need a bit of assistance with figuring out a formula for one particular step in a homework assignment, a dilemma in which my textbook has not been helpful whatsoever on.

    What needs to be accomplished, per the attached dummy file of my spreadsheet thus far, is... In column I, create a formula using the IF and VLOOKUP functions to calculate the rental charges for each instrument based on the instrumentís group code, the rental period, and the Instrument Rental Charges table. (Hint: For the IF function arguments, use one VLOOKUP function for 3 months and another for 9 months. The defined name RentalCharges has been assigned to the Instrument Rental Charges table.)

    I am indeed familiar with creating nested IFs and individual VLOOKUP formulas, but once again, I am at a complete loss for how to combine these two together into one formula! I mean, there are two columns in the 'Rentals' table (on the 'Rental Data' sheet) - 'Group Code' and 'Rental Period' - that need to be configured to determine which match from the 'Rental Charges' table (on the 'Rental Information' sheet) will be outputted into column I... trying to figure this out on my own for the past few hours has literally made me vomit and break out into a fever.
    Attached Files Attached Files

  2. #2
    Good morning,

    The hint information implies that you should use 2 vlookups, but that's not really necessary. The following will work:

    =VLOOKUP([@[Group Code]],RentalCharges,IF([@[Rental Period]]=3,2,3),FALSE)

    The table with rental charges breaks 3 months and 9 months down, but in different columns. For items rented for 3 months, the vlookup needs to pull column 2, and for 9 months, column 3. This could be accomplished several ways. The book describes something like:

    = if ( rental period = 3, vlookup( group code , rental period, 2 , false) , if ( rental period = 9, vlookup( group code , rental period, 3 , false) , [something else if false....???] ))

    This would have a nested if and 2 vlookups, but you don't need that here...

    Since the only thing modifying the vlookup is the column selection, I put the if inside the vlookup:

    =VLOOKUP([@[Group Code]],RentalCharges,IF([@[Rental Period]]=3,2,3),FALSE)

    So, if rental period = 3, the column reference is 2, otherwise it's 3 (because 9 is the only other alternative.

    Hopefully this helps.

    Best of luck,

  3. #3
    bgoree09, you are a life saver! That formula worked perfectly and accomplished exactly what I was struggling with! Thank-you so much for the assistance and advice!

  4. #4
    I cannot seem to get the formula to work, must be doing something incorrectly

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    794
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by utilityworker69 View Post
    I cannot seem to get the formula to work, must be doing something incorrectly
    When you copy a formula into a table column, sometimes it isn't immediately recognised as a formula and will remain in the cell as a text statement. If thats whats happening, try selecting the cell and press F2 to enter Edit mode. Then press Enter and see if that fixes the problem . If it doesn't, you might need to attach your workbook so we can see whats happening.

  6. #6
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,721
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Quote Originally Posted by Hercules1946 View Post
    If it doesn't, you might need to attach your workbook so we can see whats happening.
    And if so, best start a new thread with a link referring to this one ( edit it as text eventually)
    Thank you Ken for this secure forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •