IF function with multiple options

[kristikatt]

I manage a Community Theatre, which has 3 different rates (Resident, non-resident, and commercial) and 2 discounts (Non-profit and City sponsored events). Presently in our excel document that calculates their fees, there are 2 spots that have drop-downs to pick rate and discount, but I need to select both the name of the rate and the percentage. I feel like there should be a way to connect those cells, or at least make the rate cell (C6 and C9) automatically fill in to match the Pricing tier or discount title (B6 and B9)

In another summary:

B6 drops down to choose between Commercial, Nonresident Nonprofit, and Resident Nonprofit. C6 drops in 50%, 25%, 0%.
B9 drops down to choose Not Discounted, Resident Non Profit, and Joint Use/City Sponsored. C9 drops in 0%, 50%, 100%.

I would like to just choose the items in B6 and B9, and have C6 and C9 automatically fill in with their matching number...

Hopefully someone can help me out. Thanks!

[Andreas_Zopo]

oh I'm SO interested in reading the solution to this as I think I may have to do it myself later this week!

[gmr4evr1]

Place this in C6...

Please Login or Register  to view this content.

[Pete_UK]

Remove the data validation from C6 then enter this formula:

=IF(A6="","",VLOOKUP(A6,$O$8:$Q$15,3,0))

Copy this into C9, then when you change B6 or B9 (actually it is A6 or A9 as you have merged the cells), then C6 or C9 will automatically pick up the appropriate %age.

Hope this helps.

Pete

[gmr4evr1]

Sorry, forgot the other one. Put this in C9

Please Login or Register  to view this content.

[ben_hensel]

This is a pretty typical lookup operation.

Please Login or Register  to view this content.

You'll have to kill off the data validation on C9 (it's not allowing the formula) and you should probably go ahead and remove it from C6 as well.

Note this will throw the #N/A error if someone manually enters some other text string in A6/A9. (Which is disbarred by Data Validation right now anyway).

Also I recommend you remove the merged cells on A6:B6 and A9:B9. Using merged cells with formulas is bad practice -- merging and functions don't play nice with each other.

EDIT:
This is basically the same direction as what Pete advised, but I went with INDEX(MATCH) instead of VLOOKUP for construction (I basically never use V/HLOOKUP but that's just my preference), also he wrapped a checker for blank input.

[kristikatt]

You all rock. I used Pete's formula, and went ahead and got rid of the merged cells and stuck the stuff in A into B and edited accordingly.

Thanks!

[gmr4evr1]

Glad you found one you liked. It's not often you get several options to choose from.