Alternative to IF function

[marciaoaktree]

In my spreadsheet I need a formula to automatically calculate the HST/GST based on which province the purchase order is being created for. Example: I'm doing a purchase order for a company in Ontario. We need to charge 13% HST on that purchase order because that company is in Ontario. However, if we were doing up a purchase order for a company in Nova Scotia, the HST is 15%. So, I need a formula that will calculate either the GST or HST (whichever is applicable) based on whatever the HST or GST rate is for that province. I can't do IF statement because I have 14 of them, and the max allowable is 7. Can anyone help with this?

Example:

I want the HST or GST of whatever rate is applicable for the province I'm sending the Purchase Order to, to auto generate from the Subtotal in the cell above.

Can anyone help with this??

 

[Joe4]

Welcome to the Board!

Create a table of your provinces and rates, and then you a VLOOKUP function to pull back the correct one.
See: MS Excel: How to use the VLOOKUP Function (WS)

 

[marciaoaktree]

Hi Joe. If the cell beside my Tax amount is a dropdown box containing all of the provinces to choose from, can I still use this VLOOKUP Function?

 

[Joe4]

Yes.

 

[marciaoaktree]

I just tried using the vlookup function. Problem is, i need to multiply the subtotal by the gst/hst... I can't see this function working for this. Is there anything else i can do?

 

[Joe4]

It should return your percent, so you can multiply by that, either in two cells or in one cell like:

=Subtotal * VLOOKUP(...)

 

[marciaoaktree]

Thanks Joe, will give it a shot. Much appreciated.