Forum Discussion

mollylevens's avatar
mollylevens
Copper Contributor
Nov 23, 2021

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) t...
Formulas and Functions
OliverScheurich's avatar
OliverScheurich
Gold Contributor
Nov 23, 2021

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.

mollylevens's avatar
mollylevens
Copper Contributor
Nov 23, 2021
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"?
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 23, 2021

    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.

Resources