In a Google Sheets workbook, I have a sheet named Tax Codes and Rates containing a tax table with the following 4 columns: County ,  Municipality or County ,  Location Code ,  Rate

Sample Lookup Data

Sheet: 'Tax Codes and Rates'

1 County Municipality or County Location Code Rate
2 Bernalillo Santolina TID District 20 02-640 6.31%
3 Bernalillo Upper Petroglyphs TID District 1 02-607 6.31%
4 Catron Reserve 28-130 7.88%
5 Curry Remainder of County 05-005 6.00%
6 Eddy Artesia 03-205 7.77%
7 Eddy Carlsbad 03-106 7.52%
8 - ? ... ... etc. ... ...

In a separate sheet named DA Template, I want a formula that takes a Location Code as input and returns the Rate from the matching row in the Tax Codes and Rates sheet.

Sample Results

Sheet: 'DA Template'

1 Lookup Value
( Location Code )
Formula Result
( Rate )
2 05-005 6.00%
3 02-607 6.31%
4 03-106 7.52%

Using the sample tables above,

  1. Lookup value 05-005 located in 'DA Template'!A2 matches 'Tax Codes and Rates'!C5'
  2. Formula in 'DA Template'!B2 returns 6.00% the value from Rate in the same row: 'Tax Codes and Rates'!D5'

What formula would automatically return the Rate based on the Location Code?

Sample Google Sheets Workbook

2 Answers 2


Perfect use case for the VLOOKUP function.

=VLOOKUP(A2, 'Tax Codes and Rates'!C2:D35, 2, FALSE)

The accepted answer works well. I show a couple of alternatives below as well as VLOOKUP Single-Formula which extends the approach in the accepted answer to calculate the results for multiple rows.


=FILTER('Tax Codes and Rates'!D$2:D$35,
   'Tax Codes and Rates'!C$2:C$35=A2)


=INDEX('Tax Codes and Rates'!D$2:D$35,
   MATCH(A2, 'Tax Codes and Rates'!C$2:C$35, 0))

VLOOKUP Single-Formula

VLOOKUP in the accepted answer is well-suited to be extended using the ARRAYFORMULA function so that it returns results for multiple rows using a single formula.

If your lookup values are in A2:A10 you would use a single formula in B2:

   VLOOKUP(A2:A10, 'Tax Codes and Rates'!C2:D35, 2, 0))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.