Home > Topics > Fundamentals of Excel > VLOOKUP & HLOOKUP Masterclass
← PreviousNext →

VLOOKUP & HLOOKUP Masterclass

In business, you often have a large "database" (like a product list) and a "search" cell (like an invoice). Lookup functions are the bridge that automatically pulls information from the database into your search results.

1. VLOOKUP (Vertical Lookup)

VLOOKUP searches for a value in the leftmost column of a table and returns a value in the same row from a column you specify.

  • V stands for Vertical (it searches down the column).

The Syntax (Must Memorize):

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

Loading diagram…

2. Exact vs. Approximate Match

This is the most common area where students make mistakes in exams.

Loading comparison…

3. HLOOKUP (Horizontal Lookup)

HLOOKUP works exactly like VLOOKUP, but it searches across the top row instead of down the first column.

  • H stands for Horizontal.
  • Use Case: When your data headers are on the left and data flows to the right (common in yearly budget comparisons).

Exam Pattern Questions and Answers

Question 1: "Step-by-step, write a VLOOKUP formula to find the Salary of an employee with ID 105 from a table in range A1:D500, where Salary is in the 4th column." (6 Marks)

Answer: To find the salary, the formula would be: =VLOOKUP(105, $A$1:$D$500, 4, FALSE)

Explanation of the steps (Marks Breakdown):

  1. 105 (lookup_value): This is the specific Employee ID we are searching for.
  2. $A$1:$D$500 (table_array): This is the range containing all employee data. We use the $ signs (Absolute Reference) to ensure the table stays fixed if the formula is copied.
  3. 4 (col_index_num): Since 'Salary' is in the 4th column of our range (A=1, B=2, C=3, D=4), we specify 4 to pull data from that column.
  4. FALSE (range_lookup): We use FALSE because an Employee ID must be an Exact Match. We do not want the salaries of similar IDs.

Question 2: "What is the #N/A error in VLOOKUP? How can it be handled gracefully?" (4 Marks)

Answer: The #N/A error stands for 'Not Available'. It occurs when Excel cannot find the lookup_value in the first column of the table_array.

Graceful Handling: To make the report look professional and avoid the #N/A error, we can wrap the VLOOKUP in an IFERROR function.

  • Formula: =IFERROR(VLOOKUP(...), "Not Found") Instead of a messy error, the cell will now display the text "Not Found" if the ID is missing.

Summary

  • VLOOKUP = Vertical (Columns); HLOOKUP = Horizontal (Rows).
  • Always use FALSE for IDs, Names, and Codes.
  • Always use $ to lock your database range.
  • col_index_num is the count of columns from the left (A=1, B=2...).
Practical Pro Tip

VLOOKUP can only look from left to right. It cannot find an ID if the ID is in Column B and you want to pull a name from Column A. For that, you need 'INDEX & MATCH'.

Quiz Time! 🎯

Loading quiz…

← PreviousNext →