# Lookup Table Spreadsheet Examples in Excel

See how an Excel lookup table works, with VLOOKUP, XLOOKUP and INDEX MATCH examples that pull rates and details into repair-estimate line items.

A lookup table in Excel is a separate reference range that holds values you want to reuse, such as rates or part details. A formula reads a key in your estimate row and returns the matching value from that range. The result is a spreadsheet where line items stay short and the reference data lives in one place. 

- Keep reference data on its own sheet and give the key column a clear name.
- VLOOKUP, XLOOKUP and INDEX MATCH all match a key to a value in another range.
- An exact match setting stops a formula from returning a nearby wrong value.
- A rate saved once in a reference table can feed many estimate rows without retyping.
 

## What a lookup table does 

A lookup table spreadsheet splits your data into two parts: the estimate rows you fill in and a reference table you read from. The estimate row holds a key, such as a task code or a part number. The reference table holds that same key next to the details you need, such as a labor rate or a unit description. 

The lookup range is the block of cells the formula searches, and the key column is the first column it checks. Excel finds the key in that column and returns a value from another column in the same row of the range. A formula keeps the estimate row short while the full detail stays in the reference table. 

Separating the two parts makes an estimate easier to audit. You can point to the reference table and show exactly where a rate came from. Changing a value in the reference table updates every estimate row that reads it. 

## Set up the reference table 

A reference table works when each key appears once and the columns have clear headers. Put the key column on the left and keep one row per item. Label the rate column with the unit it uses, such as an hourly rate, so nobody reads it as a total. 

Turn the range into an Excel table so it grows without breaking formulas. A named range also works and makes formulas easier to read. Keep the reference table on a separate sheet from the estimate so the two do not get mixed up. 

Sorting the key column matters for approximate matching. For an exact match, sort order does not affect the result. Check for duplicate keys before you rely on the table, because a duplicate makes the returned value ambiguous. 

## VLOOKUP example for a repair estimate 

VLOOKUP searches the first column of a range and returns a value from a column number you supply. A typical estimate formula reads a task code in the line item and returns the labor rate from the reference table. The column number counts from the left edge of the lookup range. 

Set the last argument to FALSE for an exact match. With TRUE or a blank, VLOOKUP finds the nearest lower value and can return the wrong rate. Exact matching is the safer choice for codes, part numbers and named tasks. 

VLOOKUP only looks to the right of the key column. If the value you need sits to the left, either rearrange the reference table or use INDEX MATCH, which reads in any direction. A missing key returns an error, so wrap the formula in IFERROR to show a clear message. 

- Enter the key, such as a task code, in the estimate row.
- Point the lookup range at the reference table on its own sheet.
- Count the column that holds the rate or description.
- Set the match argument to FALSE for an exact match.
- Wrap the formula in IFERROR to handle a missing key.
 

## XLOOKUP and INDEX MATCH compared 

XLOOKUP takes the key, the range to search and the range to return, so no column count is needed. INDEX MATCH pairs INDEX, which returns a value at a position, with MATCH, which finds that position. Both read in any direction and both handle a reference table that changes shape. 

XLOOKUP is shorter to write and includes a built-in option for the value to show when no match exists. INDEX MATCH works in older Excel versions where XLOOKUP is unavailable. The two return the same result when pointed at the same data. 

The choice depends on the spreadsheet you must open it in and on who else edits the file. Pick one method for the whole workbook so readers do not switch between formula styles. Write the match type explicitly so the formula states what it expects. 
| Method | Search direction | Match setting | Notes | 
| VLOOKUP | Right of the key only | FALSE for exact | Column count needed | 
| XLOOKUP | Any direction | Exact by default | Short syntax, fallback value | 
| INDEX MATCH | Any direction | 0 for exact | Works in older versions |  

## Match types and what they return 

An exact match returns the value only when the key matches a row completely. A near or approximate match returns the closest value below the key in a sorted range. Estimate work with task codes and part numbers needs an exact match, because a near value is a different item. 

Approximate matching suits a banded rate table where ranges of hours or quantities map to a rate. The key column must be sorted in ascending order for that to work. Mixing the two match types in one column of formulas causes errors that are hard to spot. 

Errors such as #N/A mean the key was not found, and #REF means the range no longer exists. Handle each error with a visible message rather than hiding it. A label that says key not found tells the reader what to fix. 

## Apply it to repair-estimate line items 

A labor rate lookup for repair estimates reads a task code and returns the hourly rate for that trade. The estimate row multiplies the returned rate by the hours you enter. The rate is stored once, so a change reaches every line that uses the same code. 

Side-by-side line items keep the estimate readable: description, code, hours, rate and line total. The rate column is a formula, while the hours column is typed by hand. This layout shows the reader which figures are inputs and which are pulled from the reference table. 

Keep a short list of the details you want to discuss with a local service provider next to the estimate. Saved notes on what a rate covers help that conversation. Excel can list those notes, but a trained technician still decides the scope of any repair. 

## Protect and check the reference data 

Reference data that is edited by accident breaks every formula that reads it. Lock the reference sheet and leave only the estimate input cells open. A protected sheet with unlocked input cells keeps the layout intact while staff type hours. 

Check that each key in the estimate row exists in the reference table. A quick count of unmatched keys shows how many lines need attention. Fix the key in the estimate, not in the reference table, so the table stays valid for every other user. 

Review the reference table when a provider changes a rate or adds a task. Update the value in one place and let the formulas recalculate. A dated note beside each change keeps a record of what moved. 

## Questions about Excel lookup tables 

### What is an Excel lookup table example? 

An Excel lookup table example is a reference range on one sheet plus a formula that reads a key from your estimate row. VLOOKUP, XLOOKUP and INDEX MATCH each match that key and return a rate or description. The formula keeps the line item short while the detail stays in one place. 

### Which lookup method should I use for a lookup table sheet? 

XLOOKUP is the shortest to write and searches in any direction, but INDEX MATCH works in older Excel versions. VLOOKUP is fine when the returned value sits to the right of the key column. Pick one method for the whole workbook so the formulas stay consistent. 

### How do I stop a lookup from returning the wrong rate? 

Set the formula to an exact match so it returns a value only when the key matches completely. Duplicate keys and near matches cause wrong rates, so check the key column for repeats. Wrap the formula in IFERROR to show a clear message instead of a wrong figure. 

### Can one reference table feed many estimate rows? 

Yes, one reference table can feed every estimate row that reads it. Store each rate or description once and point each formula at the same range. A change to a rate then updates all the rows that use it. 

### What does a lookup table spreadsheet look like for repair estimates? 

A lookup table spreadsheet for estimates pairs each line item code with a task, hours, a returned labor rate and a line total. The rate column is a formula, while hours are typed input. A separate reference sheet holds the codes and rates the formula reads.
