1. Core Concept & Syntax
In real-world business analysis, data is stored in separate tables (e.g. an Orders sheet contains Product ID, while the Products catalog contains Price). VLOOKUP (Vertical Lookup) searches down the first column of a table and returns related data from another column in the matching row.
The value you are searching for (e.g. E2 or "P101").
The source table range (e.g. $A$2:$C$10). Column 1 must contain the search keys.
The column number in table_array from which to return data (e.g. 2 for Product Name, 3 for Price).
FALSE (or 0) for Exact Match; TRUE (or 1) for Approximate Match.
2. Exact Match (FALSE) Execution Flow
For distinct business entities (Product IDs, Invoices, Customer Codes, Employee IDs), always specify FALSE.
Takes lookup_value (e.g. "P103")
Scans column 1 top-to-bottom until matching "P103"
Moves across row to col 3 ➔ returns ₹18,000
3. 🔥 Live Interactive — Basic VLOOKUP Lab
Enter or select any Product ID (e.g. P101, P102, P103, P104, P105) to observe real-time vertical table retrieval:
| # | Product ID (Col 1) | Product Name (Col 2) | Price ₹ (Col 3) |
|---|---|---|---|
| 2 | P101 | Laptop | ₹55,000 |
| 3 | P102 | Mouse | ₹1,500 |
| 4 | P103 | Monitor | ₹18,000 |
| 5 | P104 | Keyboard | ₹3,000 |
| 6 | P105 | Headphones | ₹2,500 |
4. Column Index Number (col_index_num)
col_index_num is counted starting from 1 at the first column of the selected table_array, NOT from the worksheet column letter!
• If table_array is A:C ➔ A = 1, B = 2, C = 3
• If table_array is B:E ➔ B = 1, C = 2, D = 3, E = 4
Quick Practice: If your table_array is C2:F50, which column index retrieves data from Column E?
5. Lookup Range Must Start in Column 1
VLOOKUP can only search for your lookup_value in the first column of table_array. If your search keys are in Column B and you select range A2:D10, VLOOKUP will search Column A and fail with #N/A!
6. F4 Absolute Reference Locking ($A$2:$C$10)
When you write a VLOOKUP in cell E2 and drag the fill handle down to row 100, relative references shift (A2:C10 ➔ A3:C11 ➔ A4:C12), leaving top records out of the search boundary!
Highlight the table array and press F4 to lock it with dollar signs: $A$2:$C$10.
7. 🔥 Live Interactive — Copy Down Lab
Observe the difference between dragging an unlocked relative reference vs. a locked absolute reference ($) across an order batch:
| Row | Product ID | Formula Executed | Product Name | Price ₹ |
|---|---|---|---|---|
| 2 | P101 | =VLOOKUP(A2, $A$2:$C$6, 2, FALSE) | Laptop | ₹55,000 |
| 3 | P103 | =VLOOKUP(A3, $A$2:$C$6, 2, FALSE) | Monitor | ₹18,000 |
| 4 | P104 | =VLOOKUP(A4, $A$2:$C$6, 2, FALSE) | Keyboard | ₹3,000 |
8. Handling Missing Values (#N/A) with IFERROR
When searching for an ID that does not exist in the database (e.g. P999), VLOOKUP returns #N/A. Pair it with IFERROR for executive-ready presentation:
Output: #N/A
9. Approximate Match (TRUE) for Tiered Brackets
When range_lookup is set to TRUE (or omitted), VLOOKUP performs an Approximate Match. It searches down the column and finds the largest value that is less than or equal to the lookup value.
The first column of table_array MUST be sorted in ascending order (smallest to largest). If it is not sorted, VLOOKUP returns unpredictable and incorrect records.
10. Live Approximate-Match Discount Practice
Test how different customer purchase amounts fall into the volume discount brackets below:
| Minimum Purchase ₹ (Sorted Ascending) | Discount Rate (Col 2) |
|---|---|
| ₹0 | 0% |
| ₹5,000 | 5% |
| ₹10,000 | 10% |
| ₹25,000 | 15% |
| ₹50,000 | 20% |
11. VLOOKUP Debugging & Error Classification
=VLOOKUP(A2, B2:D10, 5, FALSE)
col_index_num = 5 exceeds range width (3 columns). Fix by changing index to 2 or 3.=VLOOKUP(A2, Catalog, 2) (omitted 4th argument)
TRUE, returning the wrong item on unsorted data. Fix by adding FALSE.12. Practical Multi-Sheet Business Case
In enterprise systems, product details live on Products_Master while daily transactions live on Orders_Feed. VLOOKUP joins these datasets together seamlessly.
13. 🔥 Live Final Challenge (Order Processing Join)
Populate the enterprise Order Report by performing multi-column VLOOKUPs against the Product Master table:
| Order ID | SKU Code | Product Name [Col 2] | Category [Col 3] | Unit Price ₹ [Col 4] |
|---|---|---|---|---|
| ORD-2026-001 | SKU-10 | =VLOOKUP(B2, Master, 2, FALSE) | =VLOOKUP(...) | — |
| ORD-2026-002 | SKU-20 | =VLOOKUP(B3, Master, 2, FALSE) | =VLOOKUP(...) | — |
| ORD-2026-003 | SKU-30 | =VLOOKUP(B4, Master, 2, FALSE) | =VLOOKUP(...) | — |
| ORD-2026-004 | SKU-40 | =VLOOKUP(B5, Master, 2, FALSE) | =VLOOKUP(...) | — |
14. Important VLOOKUP Limitations
- Left-to-Right Only: Cannot look to the left of the search column (lookup column must be column 1).
- Column Insertion Fragility: If a user inserts a new column in table_array, static numbers like
col_index_num = 3will return the wrong column. - Duplicate Values: If the search key appears multiple times, VLOOKUP always returns the first matching row found.
15. Quick Check & Knowledge Assessment
Knowledge Assessment: Excel VLOOKUP Function
Test your understanding of exact matches, relative vs absolute locking, column index counting, and approximate tiers.
1. What is the primary purpose of the Microsoft Excel VLOOKUP function?
16. Accuracy & Official Specification
- Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). - Case Sensitivity: VLOOKUP is not case-sensitive ("P101" matches "p101").
- Wildcards: Exact match (FALSE) supports question marks (
?for any single character) and asterisks (*for any sequence).