Monday, 22 December 2025

VLOOKUP for Beginners: Find Data from Another Sheet (Step-by-Step Guide)

 

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)


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



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

 =VLOOKUP(A2, Salary_Data!A:C, 2, FALSE)



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:

 =VLOOKUP(A2, Salary_Data!A:C, 3, FALSE)








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:

RANGE (A:C) 
-----------------------------------------------------------------------------------------------------
Rule 2: Exact Match Is Best for Beginners

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)


 =IFERROR(VLOOKUP(A2, Salary_Data!A:C, 2, FALSE), "Not Found")



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



Related Article











Sunday, 21 December 2025

Excel VBA Data Cleaning Automation – Remove Special Characters Step by Step (Beginner Guide)

 

Introduction


In real-world Excel usage, data is rarely clean. Data collected from websites, software exports, CSV files, or manual entry often contains unwanted special characters, extra spaces, mixed text cases, and blank rows. These issues make reports unreliable and dashboards inaccurate.

Manually cleaning such data every day is slow and frustrating. This is where Excel VBA automation becomes extremely useful.

In this tutorial, we will build and understand a smart Excel VBA Data Cleaning Tool that:

  • Removes special characters from Name and City

  • Keeps email format safe (@ . _)

  • Leaves numeric values unchanged

  • Deletes blank rows

  • Works with one click


This guide is written especially for beginners, with clear logic and line-by-line explanation.

You just need to copy all the VBA codes and paste them into one module, one after another. Then run the VBA code; it will remove all the special characters from the dataset and clean it for you.


What Kind of Data Problems Are We Solving?

Let’s look at a common real-life problem.

Example of Dirty Data






Problems in This Data

  • @ # % in Name

  • @ # in City

  • Multiple @ in Email

  • Extra spaces

  • Mixed text case


Our Data Cleaning Rules (Very Important)

We will apply different rules for different columns, just like real companies do.

ColumnRule
Name            Remove all special characters, Proper Case
City                    Remove all special characters, Proper Case
Email            Keep @ . _, remove other junk
Amount            Do nothing (numbers stay safe)

This is called column-wise smart cleaning.



Final Output After Cleaning

Name                CityEmail                Amount
John Doe                Delhi                    jo@hn.doemail.com                5000

✔ Clean
✔ Professional
✔ Ready for reports



Step 1: Open VBA Editor

  1. Open Excel

  2. Press ALT + F11

  3. Click Insert → Module

  4. A new module (Module1) will open

👉 All code will be placed in ONE module only



Step 2: Main VBA Macro (Controller Code)

This macro controls the entire cleaning process.

What This Macro Does

  • Loops through each row

  • Sends Name & City for text cleaning

  • Sends Email for safe email cleaning

  • Leaves Amount untouched

  • Deletes blank rows



✅ Main VBA Code

 

Sub Advanced_DataCleaning_Main()

Dim ws As Worksheet Dim lastRow As Long Dim i As Long Set ws = ActiveSheet Application.ScreenUpdating = False Application.EnableEvents = False lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row If lastRow < 1 Then GoTo ExitHandler For i = 1 To lastRow ' Column A – Name If ws.Cells(i, "A").Value <> "" Then ws.Cells(i, "A").Value = Clean_Name_City(ws.Cells(i, "A").Value) End If ' Column B – City If ws.Cells(i, "B").Value <> "" Then ws.Cells(i, "B").Value = Clean_Name_City(ws.Cells(i, "B").Value) End If ' Column C – Email If ws.Cells(i, "C").Value <> "" Then ws.Cells(i, "C").Value = Clean_Email(ws.Cells(i, "C").Value) End If ' Column D – Amount (NO CHANGE) Next i DeleteBlankRows ws MsgBox "Advanced Data Cleaning Completed Successfully!", vbInformation ExitHandler: Application.ScreenUpdating = True Application.EnableEvents = True End Sub





Step 3: How Name & City Special Characters Are Removed

This function removes everything except letters and spaces.

Logic Used

  • Loop through each character

  • Keep only A–Z, a–z, and space

  • Remove symbols like @ # % $ & *

  • Convert result to Proper Case



✅ Name & City Cleaning Function

 Function Clean_Name_City(txt As String) As String

Dim i As Long Dim result As String Dim ch As String txt = Application.WorksheetFunction.Trim(txt) For i = 1 To Len(txt) ch = Mid(txt, i, 1) If ch Like "[A-Za-z ]" Then result = result & ch End If Next i Clean_Name_City = Application.WorksheetFunction.Proper(result) End Function


Line-by-Line Explanation

  • Trim(txt) → removes extra spaces

  • For i = 1 To Len(txt) → checks each character

  • Mid(txt, i, 1) → extracts one character

  • [A-Za-z ] → allows only letters & space

  • Special characters are ignored

  • Proper() → converts text to proper case



Step 4: Safe Email Cleaning (Very Important)

Emails are special.
We must NOT remove:

  • @

  • .

  • _

Logic Used

  • Convert email to lowercase

  • Allow only a–z, 0–9, @, ., _

  • Remove everything else



✅ Email Cleaning Function

 Function Clean_Email(txt As String) As String

Dim i As Long Dim result As String Dim ch As String txt = LCase(Application.WorksheetFunction.Trim(txt)) For i = 1 To Len(txt) ch = Mid(txt, i, 1) If ch Like "[a-z0-9@._]" Then result = result & ch End If Next i Clean_Email = result End Function


Example Email Cleaning

Before:

jo@hn.doe@@mail.com

After:

jo@hn.doe@mail.com

✔ Email format preserved
✔ Junk removed



Step 5: Deleting Blank Rows

Blank rows cause issues in reports and dashboards.

What This Code Does

  • Checks each row from bottom to top

  • Deletes rows with no data



✅ Delete Blank Rows Code

 Sub DeleteBlankRows(ws As Worksheet)

Dim lastRow As Long Dim i As Long lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row For i = lastRow To 1 Step -1 If Application.WorksheetFunction.CountA(ws.Rows(i)) = 0 Then ws.Rows(i).Delete End If Next i End Sub


Step 6: How to Run the Tool

  1. Press ALT + F8

  2. Select Advanced_DataCleaning_Main

  3. Click Run



🎉 Your data is cleaned automatically.


Why This VBA Project Is Industry-Style

✔ Column-wise logic
✔ Data-type aware cleaning
✔ Safe email handling
✔ No damage to numbers
✔ Beginner-friendly
✔ Reusable automation

This is exactly how real Excel automation projects are built.



Conclusion

Excel VBA Data Cleaning Automation is a must-learn skill for anyone working with Excel regularly. This project shows how to clean messy data intelligently, not blindly.

With one click, you can:

  • Remove special characters correctly

  • Keep important formats safe

  • Prepare clean, professional datasets

This project is perfect for:

  • Excel learners

  • Office professionals

  • Bloggers

  • Automation portfolios


Related Article






Wednesday, 17 December 2025

Python Programming – Introduction



What is Python?

Python is one of the most popular and powerful programming languages used today. It is a high-level programming language known for its simple syntax, readability, and flexibility, which makes it an excellent choice for beginners as well as experienced programmers. Python allows developers to focus more on solving problems rather than dealing with complex programming rules.


History of Python

Python was created by Guido van Rossum and was first released in 1991. The main goal behind Python’s development was to create a language that is easy to read, easy to write, and efficient. Over the years, Python has continuously evolved and has become one of the most widely used programming languages across the world.


Why Learn Python?

Python is highly recommended as a first programming language because of its English-like syntax. It requires fewer lines of code compared to many other programming languages, which makes development faster and easier. Python is also an interpreted language, which helps in quick testing and debugging of programs.


Key Features of Python

Python offers many useful features that make it popular among developers:


Applications of Python

Python is used in a wide range of real-world applications, including:


Advantages of Python

Some of the main advantages of Python are:


Who Can Learn Python?

Python can be learned by anyone, regardless of their background. It is suitable for:

  • Students and beginners with no programming experience

  • Working professionals looking to upgrade their skills

  • Data analysts, engineers, and developers

  • Anyone interested in automation and problem-solving


Conclusion

Python is a versatile, powerful, and easy-to-learn programming language that is widely used across different industries. Its simplicity, flexibility, and strong community support make it an ideal choice for beginners and professionals alike. In this Python tutorial series, you will learn Python step by step with practical examples to build a strong programming foundation.







Sunday, 14 December 2025

XLOOKUP Function in Excel – Complete Guide with Examples (Beginner to Advanced)


 The XLOOKUP function in Excel is one of the most powerful and useful formulas introduced by Microsoft in recent years. It is designed to replace older lookup functions like VLOOKUP, HLOOKUP, and even INDEX + MATCH in many situations.

In real office work, Excel users often need to find values such as employee names, salaries, product prices, marks, or sales data from large tables. Earlier, this was done using VLOOKUP, which had many limitations. To solve these problems, Microsoft introduced XLOOKUP.

In this blog post, you will learn:

  • What XLOOKUP is
  • Why it is better than VLOOKUP

  • XLOOKUP syntax

  • Multiple easy and practical examples
  • Real-life use cases

This guide is perfect for beginners, students, and working professionals.

What Is XLOOKUP in Excel?

XLOOKUP is a lookup and reference function that searches for a value in one range and returns a corresponding value from another range.

Simple Definition:

XLOOKUP finds a value in a table and returns the related information from the same row or column.

It works both vertically and horizontally, making it more flexible than older lookup functions.

Why XLOOKUP Is Better Than VLOOKUP

Before XLOOKUP, most Excel users relied on VLOOKUP. However, VLOOKUP has several drawbacks.

Problems with VLOOKUP:

  • Can only search from left to right
  • Breaks when columns are inserted or deleted
  • Requires column index number
  • Returns #N/A error if value not found

Cannot return multiple values

Advantages of XLOOKUP:

  • Works in any direction
  • No column number required
  • Default exact match
  • Built-in error handling
  • Can return multiple values
  • Cleaner and easier formulas

Because of these benefits, XLOOKUP is now the recommended lookup function in Excel.    

XLOOKUP Function Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Explanation of XLOOKUP Arguments

1. lookup_value (Required)

The value you want to find.
Example: Employee ID, Product Name, Roll Number.

2. lookup_array (Required)

The range where Excel will search for the lookup value.

3. return_array (Required)

The range from which Excel will return the result.

4. if_not_found (Optional)

Value to return if the lookup value is not found.
Example: "Not Found"

5. match_mode (Optional)

  • 0 → Exact match (default)
  • -1 → Exact or next smaller
  • 1 → Exact or next larger
  • 2 → Wildcard match

6. search_mode (Optional)

  • 1 → Search first to last (default)
  • -1 → Search last to first
  • 2 → Binary search (ascending)

-2 → Binary search (descending)

Basic Example of XLOOKUP

Data Table:

Formula:

=XLOOKUP(102, A2:A4, B2:B4)

Result:

Neha

XLOOKUP vs VLOOKUP (Key Differences)



Example 1: Find Salary Using XLOOKUP

=XLOOKUP(103, A2:A4, D2:D4)

Result: 40000

No column index is required, making the formula simple and safe.


Example 2: XLOOKUP with IF NOT FOUND

=XLOOKUP(105, A2:A4, B2:B4, "Employee Not Found")

Instead of showing #N/A, Excel displays a meaningful message.


Example 3: Right-to-Left Lookup Using XLOOKUP

With XLOOKUP, you can easily search from right to left.

=XLOOKUP("IT", C2:C4, B2:B4)

This returns the employee name working in the IT department.


Example 4: Horizontal Lookup (HLOOKUP Replacement)

Data:


=XLOOKUP("Feb", B1:D1, B2:D2)

Result: 7000

XLOOKUP completely replaces HLOOKUP.


Example 5: Wildcard Lookup Using XLOOKUP

=XLOOKUP("Ra*", B2:B4, D2:D4, "Not Found", 2)

This finds values that start with “Ra”.


Example 6: Find Last Match Using XLOOKUP

Useful when data contains duplicates.

=XLOOKUP(101, A2:A10, B2:B10, , , -1)

This returns the last matching record.


Example 7: Return Multiple Values (Spill Feature)

=XLOOKUP(102, A2:A4, B2:D4)

This returns Name, Department, and Salary together.

Works in Excel 365 and Excel 2021.


XLOOKUP with Dates Example

=XLOOKUP(DATE(2024,1,1), A2:A10, B2:B10, , -1)

Used in reports where approximate date matching is required.

Common Errors in XLOOKUP

1. Range size mismatch

Lookup array and return array must be the same size.

2. XLOOKUP not available

XLOOKUP works only in:

  • Excel 365
  • Excel 2021 and later

Not available in Excel 2016 or older versions.


When Should You Use XLOOKUP?

Use XLOOKUP when:

  • You want to replace VLOOKUP
  • You need flexible lookup direction
  • You want clean error handling
  • You work with large datasets
  • You build dashboards or automation

Real-Life Uses of XLOOKUP

  • Employee salary lookup
  • Student marksheets
  • Product price lists
  • Sales and MIS reports
  • Inventory management
  • Excel automation projects

Important Tips for XLOOKUP

  • Lock ranges using $ for dashboards
  • Use default exact match when possible
  • Add meaningful IF NOT FOUND messages
  • Combine XLOOKUP with IF, SUM, FILTER


Conclusion

The XLOOKUP function in Excel is a modern, flexible, and powerful lookup solution. It removes the limitations of older functions and makes Excel formulas cleaner and easier to manage.



Featured post

Pivot Tables

Pivot Tables are one of the most useful tool in excel, and most excel experts says that this tool is the magic tool for quickly prepar...