📊 Excel Basics #19 – VLOOKUP() Function

The VLOOKUP() function is one of Excel's most popular lookup functions. It searches for a value in the first column of a table and returns a value from another column in the same row.

«Note: Although XLOOKUP() is the modern replacement for VLOOKUP(), many companies still use VLOOKUP(), making it an important function to learn.»

📌 What is the VLOOKUP() Function?
VLOOKUP stands for Vertical Lookup.
It searches vertically in the first column of a table and returns a value from a specified column.

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

📌 Arguments Explained
• lookup_value → The value you want to search for.
• table_array → The table containing the data.
• col_index_num → The column number from which to return the result.
• range_lookup
– FALSE → Exact match (recommended)
– TRUE → Approximate match

📌 Example
ID | Name | Department
101 | Rahul | IT
102 | Priya | HR
103 | Amit | Finance
104 | Neha | Marketing

To find the department of Employee ID 103:
Formula:
=VLOOKUP(103,A2:C5,3,FALSE)
Result:
Finance

📌 Using a Cell Reference
If cell E2 contains the Employee ID:
=VLOOKUP(E2,A2:C5,3,FALSE)
Changing the value in E2 automatically returns the corresponding department.

📌 Common Errors
❌ Searching in a column other than the first column.
❌ Using the wrong column index number.
❌ Forgetting to use FALSE for an exact match.
❌ Returning #N/A when the value doesn't exist.

To avoid displaying errors:
=IFERROR(VLOOKUP(E2,A2:C5,3,FALSE),"Not Found")

📌 Limitations of VLOOKUP()
• Can only search from left to right.
• Breaks if columns are inserted or deleted because the column index changes.
• Slower than newer lookup functions on very large datasets.

📌 Real-World Uses
• Find employee details using Employee ID.
• Retrieve product prices from a product list.
• Get student marks using Roll Number.
• Look up customer information.
• Match invoice details with customer records.

✅ Best Practices
• Always use FALSE for exact matches.
• Keep the lookup column as the first column in the table.
• Combine VLOOKUP() with IFERROR() for cleaner reports.
• For new Excel versions, prefer XLOOKUP() as it is more flexible and powerful.

VLOOKUP() is one of the most frequently asked Excel interview topics and remains widely used in businesses worldwide.

Double Tap ❤️ For More