Function: VLOOKUP()
🧭 Purpose:
The VLOOKUP() function (Vertical Lookup) searches for a value in the first column of a table and returns a value from another column in the same row.
It’s one of the most useful functions for searching and fetching related data from large lists — like fetching a student’s course name, employee salary, or product price.
🧮 Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Parameters:
| Parameter | Description |
|---|---|
| lookup_value | The value to search for (e.g., Student ID, Product Code). |
| table_array | The data range that contains the lookup value and the result column. |
| col_index_num | The column number (from the left of table_array) from which to return the result. |
| [range_lookup] | Optional: Use FALSE for an exact match, TRUE for an approximate match. |
📘 Explanation:
Think of VLOOKUP as asking Excel:
“Find this value in the first column, and bring me the related info from another column.”
Example:=VLOOKUP("S101", A2:D10, 3, FALSE)
→ Looks for S101 in the first column of A2:D10, and returns the value from the 3rd column of that range.
🧑🎓 Example 1: Student Fee Finder
| Student ID | Name | Course | Fees (₹) |
|---|---|---|---|
| S101 | Riya Sen | Graphic Design | 15000 |
| S102 | Kunal Das | Web Design | 18000 |
| S103 | Arnav Roy | Tally GST | 12000 |
| S104 | Sneha Ghosh | Excel Advance | 10000 |
🔹 Formula:
✅ Result → 12000
🎯 It finds S103 in column A and returns the 4th column value (Fees).
💼 Example 2: Product Price Lookup
| Product Code | Product Name | Brand | Price (₹) |
|---|---|---|---|
| P1001 | Mobile Cover | MI | 250 |
| P1002 | Earphones | Boat | 899 |
| P1003 | Power Bank | Samsung | 1200 |
| P1004 | Charger | Realme | 499 |
Formula:
✅ Result → 1200
📱 Used in retail to auto-fetch prices from master lists.
⚙️ Common Mistakes & Tips
-
Always make sure the lookup value column is the first column in the table range.
-
Use
$(absolute reference) in table ranges when copying formulas.
→ Example:=VLOOKUP(A2, $A$2:$D$10, 3, FALSE) -
For partial matches (like searching nearest value), use
TRUE.
🧩 Project Tasks for Students
🧠 Project 1: School Fee Record
Create a lookup to find course fees by Student ID.
| Student ID | Name | Course | Fees (₹) |
|---|---|---|---|
| S201 | Ritu Sharma | Excel | 8000 |
| S202 | Anil Kumar | Photoshop | 15000 |
| S203 | Sneha Das | Web Design | 18000 |
| S204 | Rahul Sen | Illustrator | 12000 |
➡️ Task: Enter a Student ID in a separate cell (say G2) and use
to display their fees.
🏪 Project 2: Grocery Price Finder
| Item Code | Item Name | Unit | Price per kg (₹) |
|---|---|---|---|
| G101 | Rice | 1kg | 60 |
| G102 | Wheat | 1kg | 55 |
| G103 | Sugar | 1kg | 48 |
| G104 | Tea | 250g | 120 |
➡️ Task: Let users type an Item Code (like “G104”) and display its Price.
💳 Project 3: Employee Salary Sheet
| Emp ID | Name | Department | Salary (₹) |
|---|---|---|---|
| E001 | Raj Malhotra | Accounts | 32000 |
| E002 | Ananya Das | HR | 28000 |
| E003 | Vikram Jain | IT | 45000 |
| E004 | Nidhi Roy | Marketing | 38000 |
➡️ Task: Create a dropdown of Emp IDs and use VLOOKUP to show the Department and Salary.
🧾 Project 4: Mobile Shop Sales Tracker
| Product Code | Model Name | Brand | MRP (₹) |
|---|---|---|---|
| M101 | Galaxy A14 | Samsung | 14500 |
| M102 | iPhone SE | Apple | 45000 |
| M103 | Narzo N55 | Realme | 12999 |
| M104 | Redmi Note 13 | Xiaomi | 14999 |
➡️ Task: Find the MRP based on Product Code entered by the user.
🏦 Project 5: Bank Loan Interest Lookup
| Loan Type Code | Loan Type | Interest Rate (%) |
|---|---|---|
| L101 | Home Loan | 8.2 |
| L102 | Car Loan | 9.5 |
| L103 | Education Loan | 7.4 |
| L104 | Personal Loan | 11.0 |
➡️ Task: When a user selects a Loan Type Code, display the Interest Rate.
💡 Hint: =VLOOKUP(G2, A2:C5, 3, FALSE)
✅ Key Tip:
If your lookup data is dynamic, use VLOOKUP() inside IFERROR() to avoid #N/A errors: