Nov 23 2021 11:06 AM
I am trying to calculate what portion of a refund would be sales tax refund only using data from two sheets within a workbook.
First I have the match the zip code (column I) from sheet 1 (refunds) to zip code (column A) in sheet 2 (CompliSalesTaxRates). If those zip codes match up and equal each other, then I need to multiply the refund amount (column E) in sheet 1 (Refunds) by the total sales tax rate (column I) on sheet 2 (CompliSalesTaxRates).
I want this total to go into Sales Tax Refund Amount (column J) on sheet 1 (refunds)
I have been working for a while to try and get a formula together and can't seem to get quite there. Would appreciate any help!
Nov 23 2021 11:38 AM
=VLOOKUP(I2,CompliSalesTaxRates!$A$2:$I$80602,9,FALSE)*E2
Is this what you are looking for? There are zipcodes with multiple StrTotalTaxRates, for example zipcode 98155. VLOOKUP finds the first match of the zipcode and selects the corresponding StrTotalTaxRate.
Nov 23 2021 11:48 AM
Nov 23 2021 12:54 PM
=VLOOKUP(I2&H2,CHOOSE({1,2},CompliSalesTaxRates!$A$2:$A$80602&CompliSalesTaxRates!$E$2:$E$80602,CompliSalesTaxRates!$I$2:$I$80602),2,FALSE)*E2
This formula ties zipcode and city togehter no matter if there are multiple StrTotalTaxRates for a zipcode. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Sometimes formula returns #NA because no exact match is found. For example in row 64 "Wildwood crest" is entered with 3 spaces. In row 263 " Slidell" is entered with a space in the beginning.