How to use VLOOKUP in Excel with two sheets (2025)

VLOOKUP is one of Excel’s most powerful functions, allowing you to search for a value in one sheet and retrieve related data from another. If you’re working with two different sheets, VLOOKUP makes it easy to connect them. In this guide, I’ll explain how to use VLOOKUP in excel with two sheets in simple steps, with tables and flowcharts for better understanding.

What is VLOOKUP?

VLOOKUP stands for Vertical Lookup. It searches for a value in the first column of a table and returns a corresponding value from another column.

Syntax of VLOOKUP

				
					=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: The value you want to search.
table_array: The range where Excel should search (must include the lookup column and return column).
col_index_num: The column number (from the table_array) containing the return value.
[range_lookup]: FALSE for exact match, TRUE for approximate match.

Step-by-Step: VLOOKUP Between Two Sheets

Example Scenario

Sheet1 (Orders): Contains Order IDs and Customer Names.
Sheet2 (Products): Contains Order IDs and Product Names.
Goal: Pull the Product Name from Sheet2 into Sheet1 based on Order ID.

Sheet1 (Orders)

Order ID	Customer Name	Product Name (To Fill)
101	John	?
102	Alice	?
103	Bob	?

Sheet2 (Products)

Order ID	Product Name
101	Laptop
102	Phone
103	Tablet

Steps to Use VLOOKUP Between Two Sheets

Open Both Sheets
- Ensure both sheets are in the same Excel workbook.
In Sheet1, Click on the Cell Where You Want the Result
- Example: Click on C2 (under “Product Name”).
Enter the VLOOKUP Formula

				
					=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)

- A2: The Order ID we’re looking up (in Sheet1).
- Sheet2!A:B: The range in Sheet2 where we search (columns A & B).
- 2: Return value from the 2nd column of the range (Product Name).
- FALSE: Ensures an exact match.
Press Enter & Drag the Formula Down
- The formula will fetch the correct Product Name from Sheet2.
Final Result in Sheet1

Order ID	Customer Name	Product Name
101	John	Laptop
102	Alice	Phone
103	Bob	Tablet

Common Errors & Fixes

Error	Reason	Solution
#N/A	Lookup value not found	Check spelling or use IFERROR to handle missing values
#REF!	Column index out of range	Ensure col_index_num is within table range
#VALUE!	Incorrect range reference	Check if the sheet name is correct

Flowchart: How to use VLOOKUP in Excel with Two Sheets

Here’s a simple visualization of how VLOOKUP fetches data from another sheet:

Start: Lookup Value in Sheet1
- You begin with a value (e.g., Order ID 101) in Sheet1 that you want to search in Sheet2.
Search in Sheet2’s First Column
- Excel scans the first column of the specified range in Sheet2 (e.g., Sheet2!A:B).
Decision: Is There a Match?
- If YES → Excel moves to the next step.
- If NO → Excel shows #N/A (meaning the value wasn’t found).
Return Corresponding Value from Column 2
- If a match is found, Excel fetches the value from the 2nd column (or any specified column) in the same row.
Show Result in Sheet1
- The retrieved value (e.g., “Laptop”) appears in the original cell in Sheet1.

Final Tips

✅ Always use absolute references ($A$2:$B$10) if copying the formula.
✅ Use IFERROR to handle missing values gracefully:

				
					=IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), "Not Found")

✅ For more flexibility, consider INDEX-MATCH or XLOOKUP (newer Excel versions).

Conclusion

VLOOKUP between two sheets is a game-changer for combining data in Excel. Follow these steps, avoid common mistakes, and use the flowchart for clarity.

How to Use VLOOKUP in Excel with Two Sheets (Step-by-Step Guide)