Forum Discussion
Need help creating a formula using certain data from two sheets within a workbook
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!
3 Replies
=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.
- Thank you so much for your response.
Is there a way to tie the name of the city on sheet 1 (refunds) to any zip codes that have multiple "StrTotalTaxRate"?=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.
