How to Use Microsoft Excel VLOOKUP for Beginners (With Examples)

How to Use Microsoft Excel VLOOKUP for Beginners

Last semester, I was helping my younger sister with her college assignment. She had two separate Excel sheets, one with student names and roll numbers and another with their marks. She was manually searching for each student’s marks one by one. After watching her waste two hours, I sat beside her and said, “Let me show you something that will save your life.”

Within 10 minutes, VLOOKUP pulled all the marks automatically. Her eyes lit up like I had done magic. That moment made me realize how many people struggle with VLOOKUP even though it’s one of the most useful functions in Excel.

I’ve been using Excel daily for the last 6 years for inventory tracking, student result management, sales reports, and even my personal budget. VLOOKUP has saved me countless hours. Let me teach you exactly how I use it in simple terms.

Why I Love VLOOKUP

Imagine you have a big list of products with their prices in one sheet, and you need to pull those prices into another sheet. Doing it manually is painful. VLOOKUP does this job automatically.

It basically says, “Hey Excel, go find this value in another table and bring me the related information.”

Real Example We’ll Use

Let’s say you run a small stationery shop. You have two sheets:

Sheet1: Product List

• Product ID
• Product Name
• Price
• Stock

Sheet2: Sales Report

• You only have Product ID and Quantity
• You want Price and Total Amount automatically

I’ll show you how to connect them using VLOOKUP.

Step-by-Step: How to Use VLOOKUP

Step 1: Prepare Your Data

Make sure your data is organized properly. The column you want to search (usually Product ID or Roll No) should be on the leftmost side of your table. This is very important.

Step 2: Basic VLOOKUP Formula

The basic structure is:

=VLOOKUP(what_you_want_to_find, where_to_look, column_number, FALSE)

Let me explain with our example.

Here’s a small table for practice:

Now in your Sales Sheet, you have Product ID, and you want Price to appear automatically.

Step 3: Writing the VLOOKUP Formula

In the Price column of the Sales sheet, write this formula:

=VLOOKUP(A2, Sheet1! $A$2:$D$100, 3, FALSE)

Let’s break it down:

• A2 → The value you want to find (Product ID)
• Sheet1! $A$2:$D$100 → The table where data is stored (use absolute reference with $ sign)
• 3 → The column number from which you want data (Price is in the 3rd column)
• FALSE → For exact match (always use this for beginners)

Press Enter and drag the formula down.

Step 4: Pulling Multiple Columns

Once you understand this, you can pull product name, price, stock—anything.

For Product Name, use column number 2; for Stock, use column number 4.

Real-Life Use Cases I’ve Done

1. School Result Management — Pulling student marks from the main sheet to the report card
2. Inventory Management — Getting product price and stock automatically in the billing sheet
3. Sales Reports — Pulling product details from the master list
4. HR Salary Sheet — Pulling employee details using Employee ID

Common Mistakes Beginners Make

• Forgetting to put FALSE at the end (this causes wrong results)
• Not keeping the lookup column on the left side
• Forgetting to use the $ sign (formula breaks when dragged)
• Using the wrong column number
• Not sorting data (not needed with FALSE, but many people get confused)
• Try VLOOKUP when data is to the left of the lookup value (use XLOOKUP or INDEX+MATCH in such cases)

I made almost all these mistakes when I started. Don’t worry if you make them too.

Pro Tips From My Experience

• Always keep your master data in a separate sheet
• Use named ranges for big tables (makes the formula cleaner)
• If VLOOKUP shows a #N/A error, it means the value is not found
• For better performance with large data, consider using XLOOKUP (newer function)

FAQ

Q1. What is the difference between TRUE and FALSE in VLOOKUP? FALSE means exact match. “TRUE” means an approximate match. For beginners, always use FALSE.

Q2. Why am I getting an #N/A error in VLOOKUP? This means Excel couldn’t find the value. Check for spelling mistakes, extra spaces, or wrong range.

Q3. Can VLOOKUP work between two different Excel files? Yes, but both files need to be open. It’s a bit tricky, though.

Q4. Is VLOOKUP case-sensitive? No, it is not case-sensitive.

Q5. What should I learn after VLOOKUP? Learn INDEX + MATCH and then XLOOKUP. They are more flexible.

Q6. Can I use VLOOKUP for text values? Yes, it works perfectly with names, roll numbers, product codes, etc.

---

Look, VLOOKUP might look scary in the beginning, but once you use it 4-5 times, it becomes second nature. I still use it almost every week.

Start with small tables like the example I gave. Practice on dummy data first. After one week, you’ll be surprised how comfortable you feel with it.

If you get stuck anywhere, whether it’s a #N/A error, the wrong column number, or anything else, just drop your problem in the comments with a screenshot if possible. I’ll help you sort it out.

This one function has genuinely made my Excel life so much easier. It’ll do the same for you too.