How to Use VLOOKUP in Excel with Simple Examples
Microsoft Excel is one of the most widely used tools for data management and analysis. Among its many powerful functions, VLOOKUP is one of the most popular and frequently used. Whether you are a student, office professional, accountant, or business owner, learning how to use VLOOKUP can save you hours of manual work.
In this guide, we will explain how to use VLOOKUP in Excel with simple examples, common mistakes, and practical use cases. If you are new to Excel or looking to strengthen your skills, mastering VLOOKUP is a great step forward.
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 terms, VLOOKUP helps you:
- Match data from different tables
- Find related information quickly
- Avoid manual searching and copying
VLOOKUP Syntax Explained
The basic syntax of VLOOKUP is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Let’s break this down:
- lookup_value – The value you want to search for
- table_array – The table where the data is located
- col_index_num – The column number from which to return the value
- range_lookup – TRUE (approximate match) or FALSE (exact match)
In most cases, you will use FALSE to get accurate results.
Simple Example of VLOOKUP
Example 1: Finding a Student’s Marks
Imagine you have the following table:
| Student ID | Name | Marks |
|---|---|---|
| 101 | John | 85 |
| 102 | Sarah | 92 |
| 103 | David | 78 |
If you want to find the marks for Student ID 102, use:
=VLOOKUP(102, A2:C4, 3, FALSE)
Result: 92
This formula tells Excel:
- Look for 102 in the first column
- Search within A2:C4
- Return the value from the 3rd column
- Use an exact match
Using VLOOKUP with Text Values
VLOOKUP also works with text.
Example 2: Finding Employee Department
| Employee Name | Department | Location |
|---|---|---|
| Alex | HR | Perth |
| Emma | Finance | Sydney |
| Liam | IT | Perth |
Formula:
=VLOOKUP(“Emma”, A2:C4, 2, FALSE)
Result: Finance
Using VLOOKUP Across Multiple Sheets
One of the most common uses of VLOOKUP is fetching data from another worksheet.
Example 3: VLOOKUP from Another Sheet
=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)
This allows you to:
- Keep data organized
- Work with large datasets
- Build professional reports
Common VLOOKUP Errors and How to Fix Them
1. #N/A Error
Occurs when Excel cannot find the lookup value.
Fix:
- Ensure exact spelling
- Check for extra spaces
- Use FALSE for exact match
2. Wrong Column Returned
Happens when the column index number is incorrect.
Fix:
- Count columns carefully from the left
3. VLOOKUP Not Working After Copying
This happens when table references shift.
Fix:
Use absolute references:
=VLOOKUP(A2, $A$2:$C$100, 3, FALSE)
Using VLOOKUP with IF Function
You can combine VLOOKUP with IF to handle missing values.
Example:
=IFERROR(VLOOKUP(A2, A2:C10, 3, FALSE), “Not Found”)
This displays “Not Found” instead of an error, making your spreadsheet more professional.
Practical Business Uses of VLOOKUP
VLOOKUP is widely used in real-world scenarios, such as:
- Salary calculations
- Invoice matching
- Stock and inventory tracking
- Student records
- Sales and customer reports
This is why VLOOKUP is considered an essential Excel skill for office and corporate roles.
VLOOKUP vs XLOOKUP (Quick Note)
While newer Excel versions offer XLOOKUP, many companies still rely on VLOOKUP. Understanding VLOOKUP ensures you can work confidently with:
- Older Excel files
- Legacy systems
- Client-provided spreadsheets
Learning VLOOKUP also makes it easier to transition to advanced Excel functions later.
Tips to Master VLOOKUP Faster
- Always sort your data properly
- Use helper columns when needed
- Practice with real datasets
- Learn error handling with IFERROR
- Combine VLOOKUP with Excel tables
For many learners, structured guidance helps avoid confusion and speeds up learning.
Why Learn VLOOKUP Through Proper Excel Training?
Although VLOOKUP looks simple, many users struggle with:
- Errors
- Complex datasets
- Combining functions
- Real-world business scenarios
That’s where professional Excel training makes a difference. With guided learning, you not only understand the formula but also how and when to use it correctly.
If you are looking to build strong Excel skills for work or career growth, enrolling in Excel training in Perth can help you gain hands-on experience with functions like VLOOKUP, Pivot Tables, and data analysis tools.
Excel Training in Perth for Practical Skills
Excel is best learned through practice. Many professionals choose Excel training in Perth programs to:
- Improve job performance
- Gain confidence in data handling
- Learn real-world business applications
- Prepare for office and corporate roles
Whether you are a beginner or an intermediate user, proper Excel training helps you move beyond basic formulas and use Excel efficiently.
Key Takeaways: Mastering VLOOKUP in Excel
VLOOKUP is one of the most important Excel functions you can learn. It helps you work faster, reduce errors, and manage data professionally. By understanding the syntax, practicing simple examples, and learning common fixes, you can confidently use VLOOKUP in everyday tasks.
If you want to go beyond basics and apply Excel skills in real-world scenarios, structured learning and practice are key. With the right guidance and Excel training in Perth, you can master Excel functions like VLOOKUP and significantly improve your productivity.
NATIONAL & INTERNATIONAL TRAINING