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

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.

லேபிள்கள்:

கருத்துகள்

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

இந்த வலைப்பதிவில் உள்ள பிரபலமான இடுகைகள்

How to improve the speed of your computer

 Is your PC is getting slow? Is it hang frequently? Is there any simple solutions to speed up your PC? Yes.. there are some small tips available , which will increase the speed of you Desktop or laptop   1) Temporary files cleaning Press Win+R button -> now Run command will come Type %temp% and hit enter   Now all the temporary files will get opened. Select all the files by pressing Ctrl + A , and press Shift + Delete button. All the files will get deleted .   2) Clean the Recycle Bin Right Click on Recycle Bin and select Empty Recycle bin. Now all the recycle bin items will get deleted permanently Note : Before deleting the Recycle bin. Go inside the Recycle bin, and check any item is required. Just Right click and select restore. Not the file will go to respective folder, from where it got deleted.   3) Terminate the less important tasks   Open the task manager by pressing Ctrl+Alt+Del Click on the processes ...
கருத்துரையிடுக
மேலும் படிக்கவும்

Basics of Computer in English

Introduction to Computers :   In today's modern world, computers have become an essential part of our lives. Whether it's for work, education, or entertainment, we rely heavily on these electronic machines to carry out various tasks. But, have you ever stopped to think about what exactly a computer is and how it works? In this article, we will discuss the basics of computers, including their components and functions. What is a computer? A computer is an electronic device that accepts input, processes data, and produces output. It is capable of performing various tasks, including calculations, data storage and retrieval, communication, and multimedia processing. At its most basic level, a computer consists of two main components: hardware and software. Hardware Components of a Computer Hardware refers to the physical components that make up a computer. The most important hardware components of a computer include: 1. Central Processing Unit (CPU) The CPU is the brain of the co...
கருத்துரையிடுக
மேலும் படிக்கவும்

MS Excel in Tamil (Introduction)

  விரிதாள் ( Microsoft Excel)              மக்களது அன்றாட தேவைகளுக்கு மட்டுமன்றி தொழில் ரீதியான தேவைகளுக்கும் பல்வேறு   கணக்குகளை செய்வது அவசியமாகும். இந்த கணக்குகளை செய்வதற்கு மக்கள் பல்வேறு உபாயங்களை பயன்படுத்துகின்றனர்.பிரச்சனைகளின் தன்மைகளை பொறுத்து பல்வேறு முறைகள் கையாளப்படுகின்றன. கணிப்பதற்கு என மனதையும் விரல்களையும் உபயோகிக்கின்றனர். சிக்கலான விஷயங்களை தீர்ப்பதற்கு எழுதித் தீர்த்தல் அல்லது கணிப் பொறிகள் பயன்படுத்தப்படுகின்றன. எனினும் இந்தக் கணித்தல் பணிகளை செம்மையாகவும் இலகுவாகவும் செய்வதற்கென Spread Sheet (EXCEL) எவ்வாறு பயன்படுத்தலாம் என்பது பற்றி அறிந்து கொள்வோம்.            முறையாகவும் எழுத்து மூலமும் கணக்குகளை செய்வதற்காக கணித தாள்களை பயன்படுத்துவது எமது வழக்கமாகும் . இத்தாள்கள் நிலைகுத்து(Columns) நிரல்களையும் கிடையான(Rows) நிரைகளையும் கொண்டுள்ளன. இதன் அடிப்படையிலேயே பெருமளவு நிரல்களையும் நிரைகளையும் கொண்ட விரித்தாள்கள் (Spread sheets) அமைக்கப்பட்டுள்ளன.        விரிந...
கருத்துரையிடுக
மேலும் படிக்கவும்