Formula with multiple conditions, if met then apply various percentage formula

[JenniferGibbons1]

Hi Guys,
Hoping you can help out. I have attached a file. If conditions are met, I would like to apply a formula.
eg B5,B7,B9 different options are selected from dropdown, a differing % will be applied depending on the options chosen. as per percent sheet.

[6StringJazzer]

Your tables do not show how engine size is used to determine the %.

[JenniferGibbons1]

Your tables do not show how engine size is used to determine the %.

Hi, the engine size is entered manually and then the table on percent sheet applies. Thanks

[6StringJazzer]

There is nothing in the tables that indicates what % to use for commercial vehicles based on engine size. The table shows

Car & 4x4 Derived Vans Light Commercial Vehicles Heavy Commercial Vehicles

[6StringJazzer]

Here is the formula that takes care of Passenger cars. It will double in size to cover Commercial.

Please Login or Register  to view this content.

[JenniferGibbons1]

Thanks 6StringJazzer, that is super. You're right, I didn't consider the commercial vehicles. I will try and figure it out from your formula.

[JenniferGibbons1]

I tried to simplify the formula by taking out the IF B5=Passenger , and adding the commercial type vehicles to the ENGINE TYPE dropdown. However it tells me that I have entered too many arguments -
=INDEX(IF(B9="2000<",Percent!$D$4:$D$7,Percent!$E$4:$E$7,Percent!$F$4:$F$7,Percent!$G$4:$G$7,Percent!$H$4:$H$7),MATCH($B$7&" Months",Percent!$C$4:$C$7,0))

[6StringJazzer]

It is better design to use labeling consistently. This becomes cleaner if the labels in the dropdown all exactly match the labels in the table. See attached update.

It's not really necessary but I made the second dropdown dependent on what was selected in the first one. Another way you could do it is to list all classes in the second dropdown, then you don't even need the first one anymore.

[JenniferGibbons1]

Thanks so much 6StringJazzer, I learnt so much from this. Works perfect.

[6StringJazzer]

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon addreputationiconsmall.jpg below their name.