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