Pulling in a list of "Taxes" based on Purchase Type

[Paradime0346]

Hi guys; I'm new to the board as I'm stumped and had to start looking externally for some help to a problem I'm facing. I am trying to create a standard form where, based on the purchase method used (e.g. Credit Card), the excel sheet will automatically pull in and apply several different surcharges, or taxes, applicable to that specific surcharge.

For example, when I input Credit Card as the purchase type, I want the invoice to pull in the following:

Credit Card Processing Fee: ($X)
State Tax: ($)
Federal Tax: ($)
Transportation ($)

My intent was to store all of the rates applicable to specific purchase types in an array, and then on the invoice sheet, when I select "Credit Card", I want all applicable rates to show with their corresponding calculations.

Apologies for not posting a document as I'm not even really sure where to start, or even if this is doable from an excel standpoint. It almost seems like it may be better suited to access, but unfortunately, don't think my users will be able to handle it in access.

Thanks! Please let me know if I can provide any amplifying information.

 

[Joe4]

Welcome to the Board!

One way would be to set up a lookup table of the various taxes and associated rates.
Then you could return those values using a VLOOKUP formula, as shown here: How to use the Excel VLOOKUP function | Exceljet

 

[Paradime0346]

Welcome to the Board!

One way would be to set up a lookup table of the various taxes and associated rates.
Then you could return those values using a VLOOKUP formula, as shown here: How to use the Excel VLOOKUP function | Exceljet

Thank you! I am family with VLookup, but had not considered using it to pull in multiple values. Ideally, I had hoped that by inputting "Credit Card", it would pull in the applicable 4 charges. If I input "Cash" it would pull in a separate set of charges, and keep the invoice sheet nice and clean (e.g. without surcharge cells that didn't apply).

I'll definitely start playing around with the VLOOKUP and see what I can do with it in this application. Thank you!!

 

[Joe4]

Note that you can enter muiltiple VLOOKUP formulas to pull in multiple values.

VBA is also another option for automation, especially if you want to do multiple things at once.

 

[Anthony47]

Another option (see the image)
Create a table with the values to lookup (B2:D10, in my example) based on the choice (B1:D1, in my example)
Than in F1 type a choice
Set in G1 the formula

=LET(Ris,INDEX(B2:D10,0,MATCH(F1,B1:D1,0)),FILTER(Ris,Ris<>""))

This will return the values listed under the choice