VLOOKUP and HLOOKUP in Excel: 9 Powerful Practical Examples
If you have ever searched through a large Excel sheet to find one employee, product price or exam result, you already understand why lookup formulas matter. In this tutorial, we will learn VLOOKUP and HLOOKUP in Excel through simple explanations and practical examples that you can reproduce yourself.
My approach at TechBikash28 is to connect formulas with the kind of work people actually do. We will start with an employee list, move to product prices and sales reports, and then practise a student marksheet. By the end, you will understand not only what to type in a formula but also why the formula returns a particular result.
Learn Excel VLOOKUP & HLOOKUP
Find the right record. Choose the right column or row. Build reports that are easier to check and maintain.
Example: Employee ID 1005 → Meera
=VLOOKUP(H2,A2:D6,2,FALSE)What You Will Learn
- How VLOOKUP searches a vertical table
- How HLOOKUP searches a horizontal table
- The difference between FALSE and TRUE matching
- Practical employee, product, discount and marksheet examples
- How IFERROR and MATCH make lookup reports more flexible
- When XLOOKUP is a better alternative
Free practice resource: A downloadable Excel workbook section is provided at the bottom of this article. Once the workbook is uploaded, the download button will open it directly.
What Are VLOOKUP and HLOOKUP?
VLOOKUP stands for Vertical Lookup. It looks for a value in the first column of a selected range and returns a value from another column in the same row. Think of a list where Employee ID is on the left and Name, Department and Salary are to its right. VLOOKUP is designed for this type of structure.
HLOOKUP stands for Horizontal Lookup. It searches the top row of a selected range and returns a value from a specified row below the match. This works well when months, subjects or other headings run across the top of a report.
VLOOKUP: Search Down
Find Employee ID 1005 in column A. Return the employee's name from column B.
Result: Meera
HLOOKUP: Search Across
Find February in the heading row. Return the revenue from the row below it.
Result: ₹1,45,000
Neither function sorts data or merges records. They retrieve a corresponding value from a table you have already prepared. That is why choosing the right lookup key and the right source range matters just as much as writing the formula.
VLOOKUP and HLOOKUP Syntax Explained
VLOOKUP Formula
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
HLOOKUP Formula
=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])
| Argument | What It Means |
|---|---|
| lookup_value | The ID, subject, month or other item to search for |
| table_array | The complete range containing both the search area and the result |
| col_index_num | VLOOKUP: return column counted from the first column of table_array |
| row_index_num | HLOOKUP: return row counted from the top row of table_array |
| range_lookup | FALSE for exact match; TRUE for approximate match |
Beginner tip:
Type FALSE when looking up Employee IDs,
Product Codes or Student IDs. If you leave the fourth
argument out, Excel uses approximate matching by default.
That is usually not what you want for unique identifiers.
Prepare the Sample Data in Excel
We will work with small, fictional tables so it is easy to follow every result. You can enter each table in its own worksheet before trying the formulas. When you create a separate example sheet, keep the same cell locations shown below.
Employee Master — A1:D6
| Employee ID | Name | Department | Salary (₹) |
|---|---|---|---|
| 1001 | Asha | Sales | 48,000 |
| 1002 | Ravi | Finance | 52,000 |
| 1003 | Neha | Operations | 65,000 |
| 1004 | Kabir | HR | 45,000 |
| 1005 | Meera | Sales | 72,000 |
Product Master — A1:D5
| Code | Product | Category | Price (₹) |
|---|---|---|---|
| P101 | Laptop | Computers | 58,000 |
| P102 | Mouse | Accessories | 850 |
| P103 | Keyboard | Accessories | 1,200 |
| P104 | Monitor | Displays | 12,500 |
Keep the first row as the heading row. Employee ID and Product Code are good lookup keys because each value appears only once in these examples. In real business data, duplicates need special attention because VLOOKUP and HLOOKUP return the first matching entry.
VLOOKUP to Find Employee Name and Salary
Imagine that an HR executive receives an Employee ID and wants to check the employee's name without scrolling through the master file. Put the Employee Master in A1:D6 and enter 1005 in H2.
Find the Employee Name
=VLOOKUP(H2,$A$2:$D$6,2,FALSE)
The formula finds 1005 in the first column of A2:D6. The number 2 tells Excel to return the value from the second column of that selected range. The corresponding value is Meera.
Find the Employee Salary
=VLOOKUP(H2,$A$2:$D$6,4,FALSE)
Nothing changed except the column index, which is now 4. Column D is the fourth column within A:D, so Excel returns the salary. The dollar signs keep the source range fixed when you copy the formula.
VLOOKUP for Product Code and Unit Price
A purchasing executive may know a product code but need the unit price before preparing a purchase order. Use the Product Master in A1:D5 and enter P103 in H2.
=VLOOKUP(H2,$A$2:$D$5,4,FALSE)
VLOOKUP locates P103 in column A, then returns the fourth column, Price. Now change H2 to P104. The same formula returns ₹12,500, the price of Monitor. This is how one formula can power a lookup field on a small order-entry or inventory sheet.
Remember: With VLOOKUP, the lookup value must appear in the first column of the selected table array. VLOOKUP cannot directly retrieve a value to the left of that lookup column.
Approximate VLOOKUP for Discount or Commission Bands
Not every lookup is based on an exact ID. Some calculations use thresholds: for example, a customer might receive a discount based on the amount purchased. The following table contains minimum purchase values and corresponding rates.
| Minimum Purchase (₹) | Discount |
|---|---|
| 0 | 0% |
| 50,000 | 2% |
| 1,00,000 | 5% |
| 2,00,000 | 8% |
Enter the table in A1:B5 in ascending order. Enter 175000 in H2. Use TRUE to find the greatest lower threshold at or below the purchase value.
=VLOOKUP(H2,$A$2:$B$5,2,TRUE)
₹1,75,000 is between ₹1,00,000 and ₹2,00,000,
so Excel returns the 5% rate.
If the rate is in H3, the discount amount can
be calculated with =H2*H3,
giving ₹8,750.
Critical rule: For approximate VLOOKUP, the first column of the lookup table must be sorted in ascending order. Otherwise, the result may be incorrect. For IDs and product codes, use FALSE rather than TRUE.
VLOOKUP with MATCH for a Flexible Return Column
One weakness of VLOOKUP is that we usually type the return column number manually. If the layout changes, a fixed number such as 4 can become unreliable. Combining VLOOKUP with MATCH makes the return column easier to control by its heading.
Use the Product Master in A1:D5. Enter P104 in H2 and the exact heading Price (₹) in H3. The MATCH function finds the position of that heading within A1:D1.
=VLOOKUP(H2,$A$1:$D$5,MATCH(H3,$A$1:$D$1,0),FALSE)
MATCH returns 4 because Price (₹) is the fourth column. VLOOKUP then finds P104 and returns its price. If you change H3 to Category, the formula returns Displays instead. This is a useful technique for user-controlled report templates.
HLOOKUP for a Monthly Revenue Report
HLOOKUP becomes useful when report headings run across columns. Consider a management report where the months are in the first row and Revenue, Orders and Returns are listed beneath them.
| Metric | Jan | Feb | Mar | Apr |
|---|---|---|---|---|
| Revenue (₹) | 1,20,000 | 1,45,000 | 1,31,000 | 1,60,000 |
| Orders | 45 | 52 | 48 | 60 |
| Returns | 2 | 1 | 4 | 3 |
Enter the table in A1:E4. Put Feb in H2. Because we select B1:E4, the first row of the lookup range contains the month names; row 2 contains Revenue.
=HLOOKUP(H2,$B$1:$E$4,2,FALSE)
If you need the number of February orders instead, change the row index to 3:
=HLOOKUP(H2,$B$1:$E$4,3,FALSE)
This approach is practical for small MIS templates with months across the top. In larger datasets, consider reorganizing raw transactions into a structured table before building a dashboard.
HLOOKUP to Retrieve a Student's Subject Marks
Teachers often maintain student marks with subjects across columns. HLOOKUP can retrieve a particular subject's marks for a selected student row. Here is a small practice dataset.
| Student | Math | Science | English | Computers |
|---|---|---|---|---|
| Rahul | 78 | 88 | 91 | 84 |
| Priya | 93 | 90 | 86 | 95 |
| Aman | 72 | 80 | 77 | 82 |
Enter the marks in A1:E4. Put Science in H2. To retrieve Rahul's Science score from the first student row, use the second row of range B1:E4.
=HLOOKUP(H2,$B$1:$E$4,2,FALSE)
Now change the row index to 3 to retrieve Priya's Science marks: the result becomes 90. This illustrates why it is important to count the return row relative to the first row of the selected table, not the worksheet itself.
HLOOKUP with MATCH for Student and Subject
In the previous example, changing the row number manually worked, but a real learning sheet should allow a teacher to choose both the student and the subject. We can make that possible by combining HLOOKUP with MATCH.
Using the same marks table in A1:E4, enter Science in H2 and Priya in H3.
=HLOOKUP(H2,$B$1:$E$4,MATCH(H3,$A$1:$A$4,0),FALSE)
The MATCH part searches for Priya in A1:A4 and returns 3. That value becomes the row index for HLOOKUP. HLOOKUP then finds Science in B1:E1 and returns the score from the third row of B1:E4.
Try changing H3 to Aman. The answer becomes 80. Try changing H2 to Computers and H3 to Rahul. The answer becomes 84. The same formula can therefore support different student-and-subject selections without rewriting the lookup.
Use IFERROR and Fix Common Lookup Problems
When a lookup value is missing, VLOOKUP or HLOOKUP with FALSE usually returns #N/A. In an internal practice sheet this can be useful feedback, but in a report shared with other users you may prefer a clear message.
Use the Employee Master in A1:D6, and enter any Employee ID into H2. The following formula uses IFERROR to display a friendly result when the lookup fails.
=IFERROR(VLOOKUP(H2,$A$2:$D$6,2,FALSE),"Employee ID not found")
You can apply exactly the same idea to HLOOKUP. With the monthly report in A1:E4 and a month selected in H2, use:
=IFERROR(HLOOKUP(H2,$B$1:$E$4,2,FALSE),"Month not found")
If H2 contains May, which is not in the sample monthly report, the result is Month not found.
Common Lookup Errors
#N/A
The search key is missing, stored with a different data type or contains extra spaces. Check the actual values first.
#REF!
The column or row index is larger than the number of columns or rows available in the selected table range.
Wrong Exact-Match Result
Check whether FALSE is selected and whether the lookup keys contain duplicates. These functions return the first match.
Wrong Approximate Result
Check the order of the search values. TRUE requires a properly sorted first lookup column or first lookup row.
Best practice: IFERROR makes a report easier to read, but it can also hide mistakes unrelated to missing records. During development, inspect the original error before deciding which message is appropriate.
VLOOKUP, HLOOKUP and XLOOKUP: Which Should You Use?
VLOOKUP and HLOOKUP remain valuable because you will find them in many existing Excel workbooks, office templates and interview exercises. Learning these formulas helps you understand how older lookup reports were built and how to maintain them.
In modern Excel, XLOOKUP often makes new formulas more straightforward. It uses separate lookup and return arrays, supports returning values from either direction and uses exact matching by default. That does not make VLOOKUP and HLOOKUP incorrect; it means you have another option when your Excel version supports it.
| Feature | VLOOKUP | HLOOKUP | XLOOKUP |
|---|---|---|---|
| Search direction | Down first column | Across first row | Vertical or horizontal |
| Return position | To the right | Rows below | Flexible |
| Default match | Approximate | Approximate | Exact |
| Availability | Older and newer Excel | Older and newer Excel | Supported modern Excel |
Equivalent Employee Lookup in XLOOKUP
=XLOOKUP(H2,$A$2:$A$6,$B$2:$B$6,"Not Found")
For a practical guide to modern lookups, read XLOOKUP in Excel: 10 Powerful Examples . That tutorial covers multiple criteria, multiple returned values, reverse searches and SUM with XLOOKUP.
Five Practice Challenges for Your Excel Workbook
Before checking the downloadable workbook, try the following questions independently. The best way to become confident with lookup formulas is to change the input and predict the result before Excel calculates it.
Challenge 1 · HR
Using Employee ID 1002, write VLOOKUP to retrieve the salary. Expected: ₹52,000.
Challenge 2 · Product
Using Product Code P101, retrieve the product category. Expected: Computers.
Challenge 3 · Discount
For a purchase of ₹2,25,000, retrieve the discount band. Expected: 8%.
Challenge 4 · Monthly Reporting
Use HLOOKUP to return April orders. Expected: 60.
Challenge 5 · Marksheet
Use HLOOKUP with MATCH to retrieve Aman's Computers score. Expected: 82.
Extra · Missing Value
Look for Employee ID 9999 and return a user-friendly message instead of #N/A.
When you are ready to practise with the worksheets, scroll to the download section at the end. The learning file will be linked in that section after it is published.
Watch the Excel Lookup Video Tutorial
Watch this TechBikash28 Excel lesson alongside the written VLOOKUP and HLOOKUP examples. Keep a practice workbook open, pause to check the selected ranges, and try changing the lookup value before continuing.
Excel Lookup — Video Lesson
Suggested Practice Sequence
Part 1: Use an Employee ID with a basic VLOOKUP.
Part 2: Change the column index to return a different field.
Part 3: Use HLOOKUP with a month-based report.
Part 4: Compare exact and approximate matches, then handle missing values using IFERROR.
If playback is restricted in the embedded player, use the direct YouTube link beneath it.
Explore Excel Web Stories
If you prefer learning in short visual steps, these Web Stories are another way to explore Excel formulas and reporting techniques. They also connect this article to the wider TechBikash28 Excel learning series.
VLOOKUP and HLOOKUP Frequently Asked Questions
What is the main difference between VLOOKUP and HLOOKUP?
VLOOKUP searches down the first column of a table. HLOOKUP searches across the first row. Both return a value from a specified position in the same table.
Should I use TRUE or FALSE?
Use FALSE for exact IDs, names or codes. Use TRUE only for an appropriate approximate lookup, with the first lookup column or row correctly sorted in ascending order.
Why is VLOOKUP giving #N/A?
The ID may not exist, may have an extra space or may have a mismatched data type such as a text number versus a numeric number. Verify the input and source values.
Can VLOOKUP search to the left?
Not directly in its standard form. VLOOKUP searches the first column of its selected range and returns to the right. INDEX with MATCH or XLOOKUP can support a left lookup.
What does the column index number mean?
It is the return column's position within table_array, counting the first selected column as 1. It is not always the worksheet's actual column letter or number.
Does HLOOKUP work when data is in columns?
HLOOKUP is intended for headings across the first row. For vertically arranged records with identifiers in the first column, VLOOKUP is generally simpler.
Can the formula return every duplicate matching record?
No. Standard VLOOKUP and HLOOKUP return the first qualifying match. For multiple matching rows, modern Excel functions such as FILTER or other methods are more suitable.
Is XLOOKUP better for a new workbook?
Often, yes, when all users have a supported version of Excel. XLOOKUP uses separate lookup and return ranges and exact matching by default. VLOOKUP and HLOOKUP remain useful for older workbooks and compatibility.
Microsoft references: VLOOKUP Documentation and HLOOKUP Documentation .
Download the VLOOKUP and HLOOKUP Excel Practice File
Practise the Examples in Excel
This is the download section promised at the start of the article. The practice workbook will contain sample employee records, product prices, approximate lookup bands, monthly reporting and marksheet exercises, along with formula questions and expected answers.
- Sample data for vertical and horizontal lookups
- Exact and approximate match exercises
- VLOOKUP with MATCH and HLOOKUP with MATCH
- IFERROR troubleshooting examples
- Useful for your own practice or following a video
The workbook link will become active after the Excel file is uploaded. Return to this section to download it.