Need help creating a formula using certain data from two sheets within a workbook

New Contributor

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

@mollylevens 

=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"?

@mollylevens 

=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.