Microsoft Excel is widely known for its ability to manage and analyze large sets of data. Among its many powerful functions, the lookup functions — VLOOKUP, HLOOKUP, and the newer XLOOKUP — are essential for finding and retrieving information efficiently.
In this post, we’ll explore how each of these functions works, their syntax, examples, and when to use them.
🔹 What Are Lookup Functions in Excel?
Lookup functions help you search for specific data in a table or range and return a related value from another column or row.
For example, imagine you have a list of employee IDs and their salaries. Instead of manually searching through the list, you can use a lookup function to find a salary instantly by entering the employee’s ID.
Excel provides three main lookup functions:
-
VLOOKUP – Vertical lookup
-
HLOOKUP – Horizontal lookup
-
XLOOKUP – The modern and more flexible replacement for both
1️⃣ VLOOKUP (Vertical Lookup)
📘 What It Does:
The VLOOKUP function searches for a value in the first column of a range and returns a value from another column in the same row.
📗 Syntax:
📙 Parameters Explained:
-
lookup_value: The value to search for
-
table_array: The range containing the data
-
col_index_num: The column number from which to return the value
-
[range_lookup]: TRUE for approximate match, FALSE for exact match
📘 Example:
Suppose you have a table:
| A (ID) | B (Name) | C (Salary) |
|---|---|---|
| 101 | John | 50000 |
| 102 | Mary | 60000 |
| 103 | Raj | 55000 |
To find Mary’s salary:
✅ Output: 60000
Pro Tip:
Always set the last argument to FALSE for exact matches.
2️⃣ HLOOKUP (Horizontal Lookup)
📘 What It Does:
The HLOOKUP function works just like VLOOKUP, but it searches horizontally — across rows instead of columns.
📗 Syntax:
📘 Example:
Suppose you have a horizontal table:
| A1 | B1 | C1 | |
|---|---|---|---|
| ID | 101 | 102 | 103 |
| Name | John | Mary | Raj |
| Salary | 50000 | 60000 | 55000 |
To find Mary’s salary:
✅ Output: 60000
When to Use:
Use HLOOKUP when your data is organized in rows rather than columns.
3️⃣ XLOOKUP (Modern Lookup Function)
Introduced in Excel 2019 and Microsoft 365, XLOOKUP is the modern alternative to both VLOOKUP and HLOOKUP. It is faster, more flexible, and easier to use.
📗 Syntax:
📘 Example:
Using the same employee table:
✅ Output: 60000
📙 Why XLOOKUP Is Better:
-
Works both vertically and horizontally
-
No need to count column numbers
-
Supports exact and partial matches
-
Can return values to the left of the lookup column (VLOOKUP cannot)
-
Allows custom “Not Found” messages
📘 Example with “Not Found” Message:
✅ Output: Employee not found
🔹 Comparison Table
| Feature | VLOOKUP | HLOOKUP | XLOOKUP |
|---|---|---|---|
| Search Direction | Vertical | Horizontal | Both |
| Match Type | Exact/Approximate | Exact/Approximate | Exact, Approximate, Wildcards |
| Search from Right to Left | ❌ No | ❌ No | ✅ Yes |
| Error Handling | Limited | Limited | Custom messages |
| Availability | All Excel versions | All Excel versions | Excel 2019 / 365+ |
🔹 Common Mistakes to Avoid
-
Forgetting FALSE in VLOOKUP for exact match
-
Not fixing ranges with
$when copying formulas -
Using wrong column or row index
-
Using XLOOKUP in older Excel versions (unsupported)
🟢 Conclusion
Mastering VLOOKUP, HLOOKUP, and XLOOKUP gives you powerful tools to analyze and retrieve data efficiently.
-
Use VLOOKUP for vertical tables
-
Use HLOOKUP for horizontal ones
-
Use XLOOKUP for everything — it’s faster, smarter, and easier
Once you understand how lookup functions work, you’ll spend less time searching through data and more time interpreting results.
கருத்துகள்
கருத்துரையிடுக