In this blog post, we will learn how to find a cell value from a different worksheet using the Excel VLOOKUP function.
Introduction
When working in Excel, data is often stored in different sheets. For example:
-
Employee details in one sheet
-
Salary or department details in another
-
Product list in one sheet and price list in another
In such cases, manually searching data is slow and error-prone.
This is where VLOOKUP becomes extremely useful.
In this beginner-friendly tutorial, you will learn:
-
What VLOOKUP is
-
How VLOOKUP works
-
How to use VLOOKUP to fetch data from another sheet
-
Common mistakes and how to fix them
What Is VLOOKUP in Excel?
VLOOKUP stands for Vertical Lookup.
It is used to:
Search for a value in the first column of a table and return a corresponding value from another column in the same row.
In simple words:
👉 “Find something in one sheet and bring related data from another sheet.”
VLOOKUP Syntax (Very Important)
|
|
Explanation:
-
lookup_value → Value you want to search
-
table_array → Data range where Excel will search
-
col_index_num → Column number to return data from
-
range_lookup →
-
FALSE= Exact match (recommended for beginners) -
TRUE= Approximate match
-
Example Scenario (Real-Life Example)
Sheet 1: Employee_List
Sheet 2: Salary_Data
🎯 Goal:
Bring Salary from Salary_Data sheet into Employee_List sheet using VLOOKUP.
Step-by-Step: VLOOKUP from Another Sheet
Step 1: Go to the Result Cell
In Employee_List, insert a new column named Salary.
Click on the first salary cell (for Emp ID 101).
Step 2: Write the VLOOKUP Formula
|
|
Step 3: Understand This Formula
-
A2→ Emp ID you are searching -
Salary_Data!A:C→ Lookup table from another sheet -
2→ Salary column (2nd column in table) -
FALSE→ Exact match
Press Enter ✔️
Step 4: Copy the Formula Down
Drag the formula down to fetch salary for all employees.
🎉 Done! Salary is automatically fetched from another sheet.
Fetching Another Column (Department)
To fetch Department, use:
|
|
Here:
-
Column 3 = Department
Important Rules of VLOOKUP (Must Know)
Rule 1: Lookup Column Must Be First
VLOOKUP always searches only in the first column of the selected range.
❌ Wrong:
RANGE (B:C)
✅ Correct:
Always use: False
This avoids wrong results.
----------------------------------------------------------------------------------------------------
Rule 3: Column Number Is Relative
Column numbers are counted inside the table range, not the sheet.
-------------------------------------------------------------------------------------------------
Common VLOOKUP Errors & Fixes
❌ Error 1: #N/A
Reason:
Lookup value not found.
Fix:
-
Check spelling
-
Remove extra spaces
-
Use exact match (
FALSE)
❌ Error 2: #REF!
Reason:
Wrong column number.
Fix:
Make sure col_index_num is within table range.
---------------------------------------------------------------------------------------------
❌ Error 3: Wrong Result
Reason:
Using TRUE instead of FALSE.
Fix:
Always use: False
Using IFERROR with VLOOKUP (Recommended)
|
|
This makes your report look clean and professional.
Why Use VLOOKUP from Another Sheet?
✔ Saves time
✔ Avoids manual searching
✔ Reduces errors
✔ Essential office skill
✔ Used in HR, Sales, Finance, Inventory
Real-World Use Cases
-
Employee salary lookup
-
Student marksheet automation
-
Product price lookup
-
GST rate lookup
-
Inventory stock details
Limitations of VLOOKUP (Beginner Awareness)
-
Cannot lookup right to left
-
Breaks if columns move
-
Slower for very large data
👉 These issues are solved using XLOOKUP or INDEX-MATCH (future topics).
Final Thoughts
VLOOKUP is one of the most important Excel functions for beginners.
Once you master VLOOKUP from another sheet, you unlock:
-
Faster reporting
-
Better automation
-
Professional Excel skills
No comments:
Post a Comment