Microsoft Excel has many powerful functions that help users organize and analyze data. Two of the most useful functions for searching data in a table are VLOOKUP and HLOOKUP. These functions allow you to quickly find specific information in large datasets without manually searching through rows or columns.
In this easy-to-understand guide, you will learn what VLOOKUP and HLOOKUP are, how they work, and how to use them step-by-step with simple examples.
What is VLOOKUP in Excel?
VLOOKUP stands for Vertical Lookup. It is used to search for a value in the first column of a table and return a corresponding value from another column in the same row.
In simple terms, VLOOKUP helps you find information vertically (top to bottom) in a table.
VLOOKUP Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Explanation of Each Argument
- lookup_value – The value you want to search for.
- table_array – The range of cells that contains the data.
- col_index_num – The column number where Excel should return the result.
- range_lookup – Optional. Use:
- FALSE for exact match
- TRUE for approximate match
Most of the time, you will use FALSE to get an exact match.
Example of VLOOKUP
Suppose you have the following table of students and their scores.
| ID | Name | Score |
| 101 | Ana | 90 |
| 102 | Ben | 85 |
| 103 | Carla | 88 |
| 104 | David | 92 |
Now you want Excel to find the score of student ID 103.
Step 1: Use the VLOOKUP Formula
=VLOOKUP(103, A2:C5, 3, FALSE)
Step 2: Understand the Formula
- 103 → the ID you want to search
- A2:C5 → the table containing the data
- 3 → the Score column (third column)
- FALSE → exact match
Result
Excel will return: 88
Because student ID 103 has a score of 88.
When Should You Use VLOOKUP?
VLOOKUP is useful when you want to:
- Find product prices
- Retrieve student grades
- Look up employee information
- Match IDs with names
- Search large data tables
Many businesses use VLOOKUP to quickly retrieve data from large Excel sheets.
Common VLOOKUP Mistakes
1. Lookup Value Not in First Column
VLOOKUP only searches the first column of the selected table.
Incorrect table example:
| Name | ID | Score |
If you try to search by ID, VLOOKUP will not work because ID is not the first column.
2. Wrong Column Index Number
If you select the wrong column number, Excel will return incorrect data.
Example:
=VLOOKUP(103, A2:C5, 2, FALSE)
This will return the Name instead of the Score.
3. Forgetting FALSE for Exact Match
Without FALSE, Excel may return incorrect results.
Best practice:
=VLOOKUP(lookup_value, table, column, FALSE)
What is HLOOKUP in Excel?
HLOOKUP stands for Horizontal Lookup. It searches for a value in the first row of a table and returns a value from a specified row.
In simple terms, HLOOKUP searches data horizontally (left to right).
HLOOKUP Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Explanation of Each Argument
- lookup_value – The value you want to find
- table_array – The data table
- row_index_num – The row number that contains the result
- range_lookup – TRUE or FALSE (usually FALSE)
Example of HLOOKUP
Suppose you have this table:
| Product | Laptop | Phone | Tablet |
| Price | 50000 | 20000 | 15000 |
| Stock | 10 | 25 | 18 |
Now you want to find the price of Phone.
Formula
=HLOOKUP(“Phone”, A1:D3, 2, FALSE)
Explanation
- “Phone” → the value you want to find
- A1:D3 → the table range
- 2 → second row (Price)
- FALSE → exact match
Result: 20000
Difference Between VLOOKUP and HLOOKUP
| Feature | VLOOKUP | HLOOKUP |
| Search Direction | Vertical | Horizontal |
| Lookup Position | First Column | First Row |
| Returns Value From | Column | Row |
| Most Common Use | Yes | Rarely used |
In most cases, people use VLOOKUP more often than HLOOKUP because data is usually arranged vertically in Excel tables.
Real-Life Example: Product Price Lookup
Imagine you have a product list.
| Product ID | Product Name | Price |
| P100 | Keyboard | 800 |
| P101 | Mouse | 400 |
| P102 | Monitor | 6500 |
To find the price of P101:
=VLOOKUP(“P101”, A2:C4, 3, FALSE)
Excel will return: 400
This saves time compared to manually searching for the value.
Tips for Using VLOOKUP and HLOOKUP
1. Use Exact Match
Always use FALSE unless you specifically need an approximate result.
=VLOOKUP(A2, A2:D20, 3, FALSE)
2. Lock the Table Range
Use absolute references so the formula works when copied.
Example:
=VLOOKUP(A2, $A$2:$D$20, 3, FALSE)
The $ prevents the table range from changing.
3. Use Named Tables
Using tables makes formulas easier to understand.
Example:
=VLOOKUP(A2, Products, 3, FALSE)
When VLOOKUP and HLOOKUP Are Not Enough
Newer Excel versions include a more powerful function called XLOOKUP, which improves on both VLOOKUP and HLOOKUP.
Advantages of XLOOKUP:
- Can search left or right
- No column index needed
- More flexible
However, VLOOKUP is still widely used, especially in older Excel versions and workplaces.
Final Thoughts
VLOOKUP and HLOOKUP are essential Excel functions that help users quickly find information inside large datasets.
To summarize:
- VLOOKUP searches data vertically (top to bottom).
- HLOOKUP searches data horizontally (left to right).
- Both functions save time and reduce manual work.
- They are commonly used in business, school, and data analysis.
If you regularly work with Excel spreadsheets, learning these functions will significantly improve your productivity. With practice, you will be able to retrieve information from large tables in seconds.
Read Also: IF, AND, and OR Functions in Excel 2021: Easy Guide with Examples