Whether you’re retrieving product prices, finding matching entries across sheets, or just trying to avoid the dreaded #N/A error, lookup functions are at the heart of Excel data retrieval. Let’s dive into how each function works, where they’re best used, and why XLOOKUP is now the superstar of the bunch.
📌 1. VLOOKUP – The Vertical Classic
Function:
Looks for a value in the first column of a range and returns a corresponding value from another column in the same row.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example:
Find the price of “Apple” from a table:
| Fruit | Price |
|---|---|
| Apple | 100 |
| Banana | 50 |
=VLOOKUP("Apple", A2:B3, 2, FALSE)
Result: 100
📌 2. HLOOKUP – Horizontal Lookup
Function:
Similar to VLOOKUP, but works across rows instead of columns.
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Example:
Find the price of “Apple” in a horizontal layout:
| Apple | Banana | |
|---|---|---|
| Price | 100 | 50 |
=HLOOKUP("Apple", A1:C2, 2, FALSE)
Result: 100
📌 3. LOOKUP – The Old Reliable (But Limited)
Function:
Finds a value in a one-row or one-column range and returns a value from the same position in another range. It only works if data is sorted in ascending order.
Syntax:
=LOOKUP(lookup_value, lookup_vector, result_vector)
Example:
Find the price of “Banana”:
=LOOKUP("Banana", A2:A3, B2:B3)
Result: 50
🔧 Limitation: Data must be sorted! Otherwise, it may return incorrect results.
🌟 4. XLOOKUP – The Modern Hero
Function:
Replaces VLOOKUP, HLOOKUP, and LOOKUP with a single, powerful function that works both vertically and horizontally, and can return defaults for missing values.
Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Example:
Find the price of “Apple”:
=XLOOKUP("Apple", A2:A3, B2:B3, "Not found")
Result: 100
✅ Bonus Features:
- No need to count column numbers
- Works left-to-right or right-to-left
- Built-in error handling
- Dynamic and scalable for big datasets
🆚 Feature Comparison
| Feature | VLOOKUP | HLOOKUP | LOOKUP | XLOOKUP |
|---|---|---|---|---|
| Direction | Vertical only | Horizontal only | Sorted lookup | Both directions |
| Column flexibility | Fixed index | Fixed index | Limited | Dynamic |
| Error Handling | Manual | Manual | None | Built-in if_not_found |
| Sorted Data Needed | No | No | Yes | No |
| Can Search Left | ❌ | ❌ | ❌ | ✅ |
| Replacement Status | Obsolete | Obsolete | Obsolete | Recommended |
📝 Conclusion
While VLOOKUP, HLOOKUP, and LOOKUP have served generations of spreadsheet users, XLOOKUP is now the recommended choice for modern Excel workflows. It simplifies syntax, eliminates common errors, and adapts beautifully to dynamic, scalable spreadsheets.
Still using VLOOKUP out of habit? It might be time for an upgrade—your future self will thank you.