Theoretical: VLOOKUP & HLOOKUP Syntax
VLOOKUP (Vertical Lookup) Searches for a value in the first column of a table and returns a value from the specified column.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value: The value to search for
- table_array: The range containing the data
- col_index_num: Column number to return (1 = first column)
- range_lookup: FALSE for exact match, TRUE for approximate
HLOOKUP (Horizontal Lookup) Searches for a value in the first row of a table and returns a value from the specified row.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])---
Student Table (A1:E10)
| Roll No | Name | Course | Marks | Grade |
|---|---|---|---|---|
| 101 | Aman | BCA | 85 | A |
| 102 | Priya | MCA | 72 | B |
| 103 | Rohit | BCA | 91 | A |
| 104 | Neha | BSc | 65 | C |
| 105 | Vikas | MCA | 78 | B |
| 106 | Simran | BCA | 88 | A |
| 107 | Dev | BSc | 60 | C |
Product Table (A12:C17)
| Product | Price (₹) | Stock |
|---|---|---|
| Pen | 10 | 500 |
| Notebook | 50 | 200 |
| Bag | 350 | 80 |
| Eraser | 5 | 1000 |
| Ruler | 20 | 300 |
---
VLOOKUP Solutions
=== Q1. Name of student with Roll No 103 ===
=VLOOKUP(103, A2:E8, 2, FALSE)
→ Result: Rohit
=== Q2. Marks of student with Roll No 105 ===
=VLOOKUP(105, A2:E8, 4, FALSE)
→ Result: 78
=== Q3. Grade of student Aman ===
=VLOOKUP("Aman", B2:E8, 4, FALSE)
→ Result: A
=== Q4. Course of student Rohit ===
=VLOOKUP("Rohit", B2:E8, 2, FALSE)
→ Result: BCA
=== Q5. Marks of Roll No entered in G2 ===
=VLOOKUP(G2, A2:E8, 4, FALSE)
=== Q6. Price of Notebook ===
=VLOOKUP("Notebook", A13:C17, 2, FALSE)
→ Result: 50
=== Q7. Stock of Bag ===
=VLOOKUP("Bag", A13:C17, 3, FALSE)
→ Result: 80
=== Q8. Price of product entered in G10 ===
=VLOOKUP(G10, A13:C17, 2, FALSE)
=== Q9. Stock of product entered in G10 ===
=VLOOKUP(G10, A13:C17, 3, FALSE)---
VLOOKUP Lookup Section (G2:H range)
=== Name of student whose Roll No is in G2 ===
=VLOOKUP(G2, A2:E8, 2, FALSE)
=== Course of student whose Roll No is in G3 ===
=VLOOKUP(G3, A2:E8, 3, FALSE)
=== Marks of Roll No 106 ===
=VLOOKUP(106, A2:E8, 4, FALSE)
→ Result: 88
=== Grade of Roll No 101 ===
=VLOOKUP(101, A2:E8, 5, FALSE)
→ Result: A
=== Marks of student Simran ===
=VLOOKUP("Simran", B2:E8, 3, FALSE)
→ Result: 88---
Product Table (Horizontal Layout for HLOOKUP) — Row 20 onwards
| Row | Pen | Notebook | Bag | Eraser | Ruler |
|---|---|---|---|---|---|
| Product | Pen | Notebook | Bag | Eraser | Ruler |
| Price | 10 | 50 | 350 | 5 | 20 |
| Stock | 500 | 200 | 80 | 1000 | 300 |
HLOOKUP Solutions
=== Price of Pen ===
=HLOOKUP("Pen", B20:F22, 2, FALSE)
→ Result: 10
=== Stock of Notebook ===
=HLOOKUP("Notebook", B20:F22, 3, FALSE)
→ Result: 200
=== Price of product in G10 ===
=HLOOKUP(G10, B20:F22, 2, FALSE)
=== Stock of product in G10 ===
=HLOOKUP(G10, B20:F22, 3, FALSE)
=== Display "Not Found" if product in G11 does not exist ===
=IFERROR(HLOOKUP(G11, B20:F22, 2, FALSE), "Not Found")