# Formula to autofill value from a list on a separate sheet

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'`

A B C D
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%
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'`

A B
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`?

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

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

#### INDEX and MATCH

``````=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`:

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