Mastering VLOOKUP and HLOOKUP in Excel: A Complete Guide with Examples

---

✅ What is VLOOKUP in Excel?

VLOOKUP stands for Vertical Lookup. It searches for a value in the first column of a table and returns a value in the same row from another column.

Syntax:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Arguments:

• lookup_value: The value to search for.
• table_array: The table range to search within.
• col_index_num: The column number in the table from which to retrieve the value.
• range_lookup: Optional. TRUE for approximate match, FALSE for exact match.

---

✅ VLOOKUP Example:

Imagine this table in range A2:C6:

Employee ID	Name	Department
101	Raj	HR
102	Simran	IT
103	Aman	Marketing
104	Preeti	Finance
105	Ramesh	Admin

🔍 Goal: Find the Department of Employee ID 103.

🧮 Formula:

=VLOOKUP(103, A2:C6, 3, FALSE)

✅ Output:

Marketing

💡Why? VLOOKUP searched for 103 in column A, found it in row 4, then returned the value in the 3rd column of that row (C4).

---

✅ What is HLOOKUP in Excel?

HLOOKUP stands for Horizontal Lookup. It searches for a value in the first row of a table and returns a value in the same column from another row.

Syntax:

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Arguments:

• lookup_value: The value to find in the first row.
• table_array: The range that contains the data.
• row_index_num: The row number in the table from which to return a value.
• range_lookup: Optional. TRUE for approximate match, FALSE for exact match.

---

✅ HLOOKUP Example:

Imagine this table in range A1:F3:

ID	101	102	103	104	105
Name	Raj	Simran	Aman	Preeti	Ramesh
Dept	HR	IT	Marketing	Finance	Admin

🔍 Goal: Find the Name of Employee ID 104.

🧮 Formula:

=HLOOKUP(104, A1:F3, 2, FALSE)

✅ Output:

Preeti

💡Why? HLOOKUP searched for 104 in row 1, found it in column E, and returned the value in the 2nd row of that column (E2).

---

🆚 Key Differences: VLOOKUP vs HLOOKUP

Feature	VLOOKUP	HLOOKUP
Orientation	Vertical (columns)	Horizontal (rows)
Lookup in	First column	First row
Output from	A specified column	A specified row
Use case	When data is arranged vertically	When data is arranged horizontally

---

🔄 Tips:

• Use FALSE in range_lookup to ensure exact matches.
• Use named ranges or TABLES for dynamic data.
• VLOOKUP cannot look left. Use INDEX-MATCH for more flexibility.

---

---

🔹 Job Interview Questions on VLOOKUP & HLOOKUP

✅ Basic Level

1. What is the difference between VLOOKUP and HLOOKUP in Excel?
   (Expected: VLOOKUP searches vertically, HLOOKUP searches horizontally.)
2. What does the col_index_num in VLOOKUP do?
   (Expected: It specifies the column number from which the value is returned.)
3. What happens if range_lookup is set to TRUE vs FALSE in VLOOKUP/HLOOKUP?
   (Expected: TRUE gives approximate match, FALSE gives exact match.)
4. Can VLOOKUP return values to the left of the lookup column? Why or why not?
   (Expected: No, because VLOOKUP can only return values from columns to the right.)
5. Write a VLOOKUP formula to fetch the salary of Employee ID 102 from a given table.
   (Expect the candidate to form a valid VLOOKUP formula based on assumed columns.)

---

✅ Intermediate Level

6. What error do you get if VLOOKUP cannot find the lookup value? How do you handle it?
   (Expected: #N/A error. Use IFERROR or IFNA to handle it gracefully.)
7. What are the limitations of VLOOKUP, and how can they be overcome?
   (Expected: Can’t search left, slower in large datasets; can use INDEX-MATCH instead.)
8. When would you prefer HLOOKUP over VLOOKUP? Give a practical example.
   (Expected: When data is structured in rows instead of columns — e.g., monthly sales in a horizontal table.)

---

✅ Advanced Level

9. How would you dynamically look up data when the column index keeps changing?
   (Expected: Use MATCH() inside VLOOKUP or switch to INDEX-MATCH.) Example: =VLOOKUP("Product A", A1:D10, MATCH("Price", A1:D1, 0), FALSE)
10. Can you perform a case-sensitive lookup using VLOOKUP or HLOOKUP?
    (Expected: No, they are not case-sensitive. Use INDEX, MATCH, EXACT, or array formulas for case-sensitive search.)

---

Here’s your Excel practice file for VLOOKUP and HLOOKUP, complete with data and instructions:

📘 Contents:

• VLOOKUP_Data: A vertical table to practice VLOOKUP.
• HLOOKUP_Data: A horizontal table to practice HLOOKUP.
• Instructions: A guide on how to use the file for practice.

---

Watch the Video on Vlookup and Hlookup

---

---