
π VLOOKUP vs. HLOOKUP β Which Function Should You Use?
The most commonly used functions for finding data in Excel are VLOOKUP and HLOOKUP. Although the two functions look similar, they differ in vertical versus horizontal lookup direction.
β VLOOKUP Basics
This is a vertical lookup function.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example: Find an employee name using an employee ID
=VLOOKUP("E102", A2:C100, 2, FALSE)
β HLOOKUP Basics
This is a horizontal lookup function.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Example: Find March sales in a monthly sales table
=HLOOKUP("March", A1:M3, 2, FALSE)
β VLOOKUP vs. HLOOKUP Comparison
| Category | VLOOKUP | HLOOKUP |
|---|---|---|
| Lookup direction | Vertical | Horizontal |
| Common use | Employee information by employee ID; prices by product code | Monthly sales; exam score tables |
| Table structure | Lookup values in the first column | Lookup values in the first row |
β Practical Example 1: Find a Product Price (VLOOKUP)
Retrieve the price for product code P102
=VLOOKUP("P102", ProductTable, 3, FALSE)
β Practical Example 2: Find a Studentβs Score in an Exam Score Table (HLOOKUP)
Retrieve Hong Gil-dongβs score for subject 2
=HLOOKUP("Hong Gil-dong", ScoreTable, 3, FALSE)
β Error-Prevention Tips
- #N/A error: The lookup value was not found β Check whether the lookup value is correct.
- #REF! error: The column or row index is outside the range β Check the index number.
- Approximate-match issues: Set [range_lookup] to FALSE.
π Summary
- π VLOOKUP: Vertical lookup
- π HLOOKUP: Horizontal lookup
- π Range lookup argument: FALSE (exact match) is recommended
- π Error prevention: Always check for #N/A and #REF! errors
π Frequently Asked Questions (FAQ)
Q1. Is there a better function than VLOOKUP/HLOOKUP?
A. Yes. XLOOKUP is a newer function that is more convenient and powerful.
Q2. Can VLOOKUP look up data to the left?
A. Not by default, but it is possible with an INDEX + MATCH combination.
Q3. Are VLOOKUP and HLOOKUP case-sensitive?
A. No, they are not case-sensitive by default.
Q4. Why is HLOOKUP used less often?
A. Because data is often organized vertically, VLOOKUP is more commonly used.
Using VLOOKUP and HLOOKUP appropriately can more than double the speed of your data lookups. Try applying them to your work today π