You have a list of order numbers in one spreadsheet and a master database in another. You need to pull the customer name, amount, or status for each order — without copying and pasting one by one. This is one of the most common Excel tasks in business, and VLOOKUP handles it in seconds.
The Formula
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value = the value to search for | table_array = the range to search in | col_index_num = which column to return | range_lookup = FALSE for exact match
The Scenario
You have two sheets. Sheet 1 (“Orders”) has a list of Order IDs and you need to fill in the Customer Name and Amount. Sheet 2 (“Database”) has the complete records.
Sheet 1 — Orders (your working sheet)
| A | B | C | |
|---|---|---|---|
| 1 | Order ID | Customer Name | Amount |
| 2 | ORD-1042 | ||
| 3 | ORD-1015 | ||
| 4 | ORD-1038 | ||
| 5 | ORD-1027 |
Sheet 2 — Database (the source data)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order ID | Customer Name | Amount | Status |
| 2 | ORD-1010 | Acme Corp | $4,200 | Paid |
| 3 | ORD-1015 | Martinez & Sons | $1,850 | Pending |
| 4 | ORD-1023 | Lakewood LLC | $3,100 | Paid |
| 5 | ORD-1027 | Chen Industries | $6,400 | Invoiced |
| 6 | ORD-1038 | Bright Solutions | $2,750 | Paid |
| 7 | ORD-1042 | Greenfield Inc | $5,300 | Pending |
| 8 | ORD-1051 | Reyes Group | $890 | Paid |
Step-by-Step Solution
1Click cell B2 on your Orders sheet. This is where we want the Customer Name for ORD-1042.
2Enter the VLOOKUP formula:
=VLOOKUP(A2, Database!A:C, 2, FALSE)
A2 = the Order ID to look up | Database!A:C = search columns A through C on the Database sheet | 2 = return the 2nd column (Customer Name) | FALSE = exact match only
3For the Amount in C2, use the same formula but change the column number to 3:
=VLOOKUP(A2, Database!A:C, 3, FALSE)
4Copy both formulas down to rows 3, 4, and 5. Excel adjusts the lookup value automatically.
Result
| A | B | C | |
|---|---|---|---|
| 1 | Order ID | Customer Name | Amount |
| 2 | ORD-1042 | Greenfield Inc | $5,300 |
| 3 | ORD-1015 | Martinez & Sons | $1,850 |
| 4 | ORD-1038 | Bright Solutions | $2,750 |
| 5 | ORD-1027 | Chen Industries | $6,400 |
Tip
Always use FALSE as the last argument. Without it, VLOOKUP uses approximate matching, which can return wrong results silently — one of the most common spreadsheet errors in business.
Common Errors and Fixes
| Error | What It Means | Fix |
|---|---|---|
| #N/A | The lookup value wasn’t found in the source data | Check for extra spaces, typos, or mismatched formatting. Wrap the formula in IFERROR() to show a default value instead. |
| #REF! | The column number exceeds the table range | If your table_array is A:C (3 columns), your col_index_num can’t be 4 or higher. |
| Wrong result | VLOOKUP is returning data from the wrong row | Make sure the last argument is FALSE. Also ensure the lookup column (first column of the range) doesn’t have duplicates. |
| Slow performance | VLOOKUP across large datasets bogs down the workbook | Use a defined range (e.g., A1:C5000) instead of full columns (A:C). For very large datasets, consider INDEX/MATCH or VBA automation. |
Watch out
VLOOKUP can only search the first column of your table range and return a column to the right. If you need to look up a value and return a column to the left, use INDEX/MATCH instead.
IFERROR: Handling Missing Matches
When some Order IDs don’t exist in the database, VLOOKUP returns an ugly #N/A error. Wrap it in IFERROR to handle this cleanly:
=IFERROR(VLOOKUP(A2, Database!A:C, 2, FALSE), "Not Found")
Returns “Not Found” instead of #N/A when the Order ID doesn’t exist in the database.
When Formulas Aren’t Enough
VLOOKUP works well for simple matching, but many businesses hit its limits quickly:
- Thousands of lookups — formulas slow the workbook to a crawl
- Multiple matching criteria — you need to match on more than one column
- Data cleanup required — inconsistent formatting, extra spaces, abbreviations vs. full names
- Recurring process — you do this same matching task every week or month
- Cross-file matching — pulling data across multiple workbooks automatically
These are the exact problems we solve with VBA automation. A custom tool can match, clean, and consolidate your data in seconds — every time, without errors.
“David is a 10/10 pro.”
Dashboard Client · Upwork
Spending Too Much Time on Manual Lookups?
Tell us about your data matching challenge and we’ll show you how to automate it.
Start a Project