To pull matching data from another spreadsheet in Excel, use =VLOOKUP(A2, Database!A:C, 2, FALSE), where A2 is the value to find, the second argument is the range to search, 2 is the column number to return, and FALSE forces an exact match. Always use FALSE unless you specifically want approximate matching. Wrap it in IFERROR to replace #N/A with something readable: =IFERROR(VLOOKUP(A2, Database!A:C, 2, FALSE), "Not Found").
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 VLOOKUP syntax
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
Example: orders sheet and customer database
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.
What VLOOKUP returns for each order
| 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). Full columns, as in the formulas above, are fine for a small table. 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.
Common questions
Why does VLOOKUP return #N/A when I can see the value?
Three usual causes: trailing spaces, a number stored as text on one side, or the lookup value not being in the first column of the range. Try =TRIM(A2) and check with ISNUMBER.
Can VLOOKUP look to the left?
No. It only searches the first column of the range and returns to the right. Use INDEX/MATCH or XLOOKUP when you need to look leftward.
Does the column number include the lookup column?
Yes. Counting starts at the first column of the range, so the lookup column is column 1 and the first column of results is 2.
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.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.