vLookUp based on dropdown selection

So we have a set of services we sell to some vendors, in the C column I have: electrical, parking, IT, compressed air, etc., all in a data validation drop down. Column D has all the specific services for electrical, parking, IT, etc also in a modified data validation drop down. Column E has the quantity of said services. Then here is where I'm stuck, we charge an advanced rate for early buyers and a floor rate for the late comers. I was able to get a vlookup function to work but only using a single rate. I want to be able to control that function in vlookup to switch between the 2 rates of "Advanced" and "Floor" based on using the drop down:

Please Login or Register  to view this content.

Make sense?, I also want to be able to do the same thing with adding tax based on a drop down in a separate "Total" column. Maybe vlookup isn't the way to go...attached working file.

Michael

Change the formula in G12 to this:

=IF(D12="","",VLOOKUP(D12,Prices!$A$2:$C$46,IF(F12="Floor",3,2),0)*InventoryTable[[#This Row],[Quantity]])

It should automatically propagate down your table.

Hope this helps.

Pete

Originally Posted by Pete_UK

Change the formula in G12 to this:

=IF(D12="","",VLOOKUP(D12,Prices!$A$2:$C$46,IF(F12="Floor",3,2),0)*InventoryTable[[#This Row],[Quantity]])

It should automatically propagate down your table.

Hope this helps.

Pete

Perfect, thanks for your help!