முதன்மை உள்ளடக்கத்திற்குச் செல்

How to Use VLOOKUP, HLOOKUP, and XLOOKUP in Excel

 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:

  1. VLOOKUP – Vertical lookup

  2. HLOOKUP – Horizontal lookup

  3. 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:

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

📙 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)
101John50000
102Mary60000
103Raj55000

To find Mary’s salary:

=VLOOKUP("Mary", A2:C4, 3, FALSE)

✅ 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:

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

📘 Example:

Suppose you have a horizontal table:

A1B1C1
ID101102103
NameJohnMaryRaj
Salary500006000055000

To find Mary’s salary:

=HLOOKUP("Salary", A1:C3, 3, FALSE)

✅ 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:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

📘 Example:

Using the same employee table:

=XLOOKUP("Mary", B2:B4, C2:C4, "Not Found")

✅ 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:

=XLOOKUP("Sam", B2:B4, C2:C4, "Employee not found")

✅ Output: Employee not found

🔹 Comparison Table

FeatureVLOOKUPHLOOKUPXLOOKUP
Search DirectionVerticalHorizontalBoth
Match TypeExact/ApproximateExact/ApproximateExact, Approximate, Wildcards
Search from Right to Left❌ No❌ No✅ Yes
Error HandlingLimitedLimitedCustom messages
AvailabilityAll Excel versionsAll Excel versionsExcel 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.

லேபிள்கள்:

கருத்துகள்

கருத்துரையிடுக