VLOOKUP is the most-tested formula in Excel interviews. Over 46,000 jobs on Foundit.in require it. Yet most people learn VLOOKUP from YouTube and forget the syntax within a week. The fix? Practice with real datasets until it becomes muscle memory.
Here are 20 exercises — from absolute beginner to advanced — with complete answers.
How VLOOKUP Works (30-Second Recap)
=VLOOKUP(what_to_find, where_to_look, column_to_return, exact_or_approximate)
- what_to_find — the value you're searching for (e.g., employee ID)
- where_to_look — the table range (must start with the lookup column)
- column_to_return — which column number to return (1, 2, 3...)
- exact_or_approximate — always use FALSE for exact match
Beginner Exercises (1-7)
Exercise 1: Find Employee Name
Dataset: Employee table with columns: Emp ID | Name | Department | Salary
Task: Look up Emp ID "E105" and return the employee name.
Answer: =VLOOKUP("E105", A2:D50, 2, FALSE)
Exercise 2: Find Product Price
Dataset: Product list with columns: Product Code | Product Name | Price | Stock
Task: Find the price of product code "P-220".
Answer: =VLOOKUP("P-220", A2:D100, 3, FALSE)
Exercise 3: Look Up from a Cell Reference
Task: The lookup value is in cell F2 instead of hardcoded. Find the department.
Answer: =VLOOKUP(F2, A2:D50, 3, FALSE)
Exercise 4: Handle #N/A Error
Task: If the ID doesn't exist, show "Not Found" instead of #N/A.
Answer: =IFERROR(VLOOKUP(F2, A2:D50, 2, FALSE), "Not Found")
Exercise 5: Lookup Across Sheets
Task: Employee data is on "Master" sheet. Look up salary from "Report" sheet.
Answer: =VLOOKUP(A2, Master!A:D, 4, FALSE)
Exercise 6: Find City from Pincode
Dataset: Pincode | City | State
Task: Look up pincode 110001 and return the city name.
Answer: =VLOOKUP(110001, A2:C500, 2, FALSE)
Exercise 7: VLOOKUP with Sorted Data (Approximate Match)
Dataset: Tax slabs: Income | Tax Rate (sorted ascending)
Task: Find the tax rate for income ₹7,50,000.
Answer: =VLOOKUP(750000, A2:B10, 2, TRUE) — TRUE finds the nearest lower value.
Intermediate Exercises (8-14)
Exercise 8: VLOOKUP with Wildcard
Task: Find any product starting with "Laptop".
Answer: =VLOOKUP("Laptop*", B2:D100, 2, FALSE)
Exercise 9: Two-Way Lookup (VLOOKUP + MATCH)
Task: Look up employee E105's value from a dynamically selected column (column name in G1).
Answer: =VLOOKUP("E105", A1:D50, MATCH(G1, A1:D1, 0), FALSE)
Exercise 10: VLOOKUP Returning Multiple Columns
Task: Return both Name (col 2) and Salary (col 4) for one lookup.
Answer: Name: =VLOOKUP(F2, A:D, 2, FALSE) | Salary: =VLOOKUP(F2, A:D, 4, FALSE)
Exercise 11: Case-Sensitive Lookup
Problem: VLOOKUP is NOT case-sensitive. "abc" = "ABC".
Solution: Use INDEX-MATCH with EXACT: =INDEX(B2:B50, MATCH(TRUE, EXACT(A2:A50, F2), 0)) (Ctrl+Shift+Enter)
Exercise 12: VLOOKUP with Data Validation Dropdown
Task: Create a dropdown of employee IDs in F2, auto-populate name and salary.
Answer: Data → Validation → List. Then =VLOOKUP(F2, A:D, 2, FALSE) in G2.
Exercise 13: Multiple Criteria Lookup
Task: Look up salary where Name = "Rahul" AND Department = "Sales".
Answer: Use helper column: =A2&B2, then VLOOKUP on combined key. Or use INDEX-MATCH: =INDEX(D:D, MATCH(F2&G2, A:A&B:B, 0)) (Ctrl+Shift+Enter)
Exercise 14: VLOOKUP Returning Last Match
Problem: VLOOKUP returns the FIRST match. What if you need the last?
Answer: Use LOOKUP: =LOOKUP(2, 1/(A2:A100="Delhi"), B2:B100)
Advanced Exercises (15-20)
Exercise 15: Left Lookup (VLOOKUP Can't Do This)
Task: Lookup column is to the RIGHT of the return column. VLOOKUP fails.
Answer: Use INDEX-MATCH: =INDEX(A2:A50, MATCH("Sales", C2:C50, 0))
Exercise 16: Dynamic Table Range
Task: Table grows as data is added. Range should auto-expand.
Answer: Use full column references: =VLOOKUP(F2, A:D, 2, FALSE) or convert to Table (Ctrl+T).
Exercise 17: XLOOKUP (Modern Replacement)
Task: Rewrite Exercise 1 using XLOOKUP.
Answer: =XLOOKUP("E105", A2:A50, B2:B50, "Not Found") — simpler, no column number, built-in error handling.
Exercise 18: VLOOKUP in Nested IF
Task: If department is "Sales", apply 10% bonus to looked-up salary.
Answer: =IF(VLOOKUP(F2,A:D,3,FALSE)="Sales", VLOOKUP(F2,A:D,4,FALSE)*1.1, VLOOKUP(F2,A:D,4,FALSE))
Exercise 19: VLOOKUP Across Workbooks
Task: Pull data from another Excel file (must be open).
Answer: =VLOOKUP(A2, [MasterData.xlsx]Sheet1!A:D, 3, FALSE)
Exercise 20: Build a Mini Dashboard with VLOOKUP
Task: Create a dashboard where selecting an employee ID shows all details.
Answer: Dropdown in B1, then B2: =VLOOKUP($B$1,Data!A:F,2,FALSE), B3: column 3, B4: column 4, etc.
Practice VLOOKUP with Real Datasets
Reading exercises isn't enough. Verma Learning gives you actual Indian business datasets — type the VLOOKUP yourself, submit, and see if you got it right.
Download Free — vermalearning.comCommon VLOOKUP Mistakes
- Forgetting FALSE — without it, VLOOKUP does approximate match and gives wrong results.
- Lookup column not first — the lookup value MUST be in the first column of the table range.
- Extra spaces — use TRIM:
=VLOOKUP(TRIM(F2), A:D, 2, FALSE) - Number vs Text mismatch — if ID "1001" is stored as text, numeric 1001 won't match.
- Not locking the table range — use $ signs:
=VLOOKUP(F2, $A$2:$D$50, 2, FALSE)when copying down.
Why INDEX-MATCH is Better
Once you master VLOOKUP, learn INDEX-MATCH. It can look left, doesn't break when columns are inserted, and is faster on large files. Every data analyst uses it.
=INDEX(return_column, MATCH(lookup_value, lookup_column, 0))