The Scenario
We have two files (or sheets). The goal is to pull the Description from File 1 into File 2, matching rows by Part Number.
File 1 — Master List
| Part No | Description |
|---|---|
| 257340100101 | Tyre A |
| 257340200111 | Tyre B |
| 257440200102 | Tyre C |
| 257440200103 | Tyre D |
| 257440200106 | Tyre E |
File 2 — Transactions
| Part No | Rate | Qty |
|---|---|---|
| 257340100101 | 4345 | 200 |
| 257440200102 | 4400 | 400 |
| 257440200103 | 3908 | 500 |
| 257440200103 | 3400 | 230 |
Our aim: Pull the Description of each Part Number in File 2 from File 1.
Step-by-Step Guide
Identify Master vs Transaction File
File 1 is the Master (source of the Description data). File 2 is the Transaction file where we want the data to appear.
Identify the Key Column
The Key Column is the one common to both files — the column we will search by. Here it is Part No, which appears first in both files.
Name the Range in File 1
In File 1, select the entire table. In the Name Box (top-left of Excel, shows cell address), type a name such as Table and press Enter. This makes the formula cleaner and safer across files.
Enter the VLOOKUP Formula in File 2
In a blank column in File 2, type the formula below. A2 is the Part No in the current row.
— A2 : the Part No to search for (key field)
— Table : named range in File 1 (all columns)
— 2 : return column 2 of the range (Description)
— FALSE : exact match only (always use FALSE)
Understanding Each Argument
| Argument | What it Means | In Our Example |
|---|---|---|
lookup_value | The value to search for | Part No in the current row (A2) |
table_array | The range to search in | Named range "Table" in File 1 |
col_index_num | Column number to return | 2 = the Description column |
range_lookup | Exact or approximate match | FALSE = exact match (always use this) |
Troubleshooting #N/A Errors
Verify the formula
Confirm all four arguments are correct and the range name matches exactly — names are case-sensitive.
Check data types match
The key column must be the same type in both files — both numbers OR both text. Use =ISNUMBER(A2) to check. Powerutils also has a utility under Miscellaneous to detect this.
Use #N/A results as a gap report
Any #N/A result means that Part Number from File 2 does not exist in the master — this is valuable exception reporting in itself.