Nested IFs with VLOOKUP

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.

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,

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!

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

utilityworker69 said:

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

Click to expand...

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.

Hercules1946 said:

If it doesn't, you might need to attach your workbook so we can see whats happening.

Click to expand...

And if so, best start a new thread with a link referring to this one ( edit it as text eventually)