• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Tax Rate Lookup by City Name

M

mandy9

New Member
I would like to automatically pull in a tax rate based on the city name.

So BillingSheet!E27 is the cell which will ultimately display the tax rate.

BillingSheet!Q8 is the city name.

Zips! Column A list all the city names in the state of Minnesota

Zips! Column B lists all the counties associated with those cities. (I don't think this information is needed)

Zips! Column C lists the tax rates associated with the cities.

I would like BillingSheet!E27 to find the city name entered into BillingSheet!Q8 and look for it in Zips! Column A then pull the tax rate for that city listed in Zips! Column C

I currently have the Zips sheet sorted by column C, the tax rate. and was thinking I should sort them into ranges and if the name shows up in the range then sales tax(BillingSheet!E27) =.

To complicate this even more.
If BillingSheet!Q8 = Minneapolis or St. Paul I will need it to look up the tax rate based on zip code instead of the city name. For example if BillingSheet!Q8 = "Minneapolis" and Zip Code (BillingSheet!W8 = "55401" than BillingSheet!E27 = 7.775% if FALSE then BillingSheet!E27 = 7.275%. There are 34 minneapolis zip codes that will = 7.775 and the rest would all be 7.275%

Any help would be greatly appreciated!
 
Since you already have some cities with different tax rates per zip code, perhaps you can/should change the table in Zips to be by ZipCode, rather than just city/county? Ignoring that complication for now, the formula would be:
=VLOOKUP(BillingSheet!Q8,Zips!A:C,3,FALSE)

If the Zips code gets sorted by the lookup column (in this case, col A), then you can improve the efficiency of formulas by changing to:
=VLOOKUP(BillingSheet!Q8,Zips!A:C,3)
 
Hi Mandy,

Welcome to the forum..
I think, instead of city name, you should also have one more column for ZIP, to decide, which are need to go for 1st %age and which are 2%age..

Check the attached, and create a ZIP list for the same..
then in BillingSheet!E27, use formula as.. below, and confirm the formula by pressing, Ctrl + Shift + Enter, not just Enter..

=INDEX(Zips!$C$1:$C$70,MATCH(BillingSheet!$Q$8&BillingSheet!$W$8,Zips!$A$1:$A$70&Zips!$D$1:$D$70,0))
 

Attachments

Luke M said:
Since you already have some cities with different tax rates per zip code, perhaps you can/should change the table in Zips to be by ZipCode, rather than just city/county? Ignoring that complication for now, the formula would be:
=VLOOKUP(BillingSheet!Q8,Zips!A:C,3,FALSE)

If the Zips code gets sorted by the lookup column (in this case, col A), then you can improve the efficiency of formulas by changing to:
=VLOOKUP(BillingSheet!Q8,Zips!A:C,3)
Click to expand...

Can I ask what the 3 in A:C,3,False represents so I know in the future?
 
After reading Deb's articles, you should find out that the 3 argument in VLOOKUP controls which column of data you want to return information from. :)
 
You must log in or register to reply here.
Back
Top