Both HLOOKUP and VLOOKUP are essential Excel functions that help you find and return specific data in a worksheet. These lookup formulas make it easy to search large tables and extract related information without scrolling through rows or columns manually.
The main distinction is in their direction of search. VLOOKUP looks for values vertically in a column, and HLOOKUP searches horizontally in a row. Understanding how they work helps you choose the right one based on the layout of your data.
In this article, you’ll learn how each function works, what makes them different, and how to use them.
➤ HLOOKUP searches data horizontally across rows, while VLOOKUP searches vertically down columns.
➤ Both functions return a related value based on a lookup item, but their search direction and reference points differ.
➤ VLOOKUP is used more often because most Excel tables are arranged in vertical order.
➤ HLOOKUP is ideal for horizontally structured data, such as grade sheets or monthly comparison tables.
➤ Both functions need the lookup value to be placed in the first row for HLOOKUP or the first column for VLOOKUP within the selected range.
Definition of HLOOKUP & VLOOKUP Functions in Excel
Let’s start by understanding what each of these lookup functions does and how they operate in Excel.
What Is HLOOKUP in Excel?
The HLOOKUP function stands for Horizontal Lookup. It searches for a value in the first row of a table or range and returns a related value from another row in the same column. This function works best when your data is organized horizontally, such as months listed across the top or categories spread along a row.
What Is VLOOKUP in Excel?
The VLOOKUP function stands for Vertical Lookup. It looks for a value in the first column of a table or range and returns a related value from another column in the same row. This function is perfect for vertically structured data, such as employee lists, product catalogs, or price sheets.
Syntax Comparison Between HLOOKUP and VLOOKUP
What sets HLOOKUP and VLOOKUP apart is mainly the way they read data and the position of the arguments in their syntax. Let’s look at both in detail.
Syntax of the VLOOKUP Function
A basic VLOOKUP formula looks like this:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Let’s understand each argument:
- lookup_value: The value that you want Excel to find in the first column of the table.
- table_array: The range of cells that contains both the lookup value and the data to be returned.
- col_index_num: The column number (starting from 1) that holds the result you want to retrieve.
- [range_lookup]: This argument defines the type of match. Enter FALSE for an exact match or TRUE for an approximate match.
Syntax of the HLOOKUP Function
A basic HLOOKUP formula is structured as follows:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Let’s understand the arguments:
lookup_value: The value that Excel should look for in the first row of the table.
table_array: The range that includes the lookup value and the data you want to extract.
row_index_num: The row number (starting from 1) that contains the return value.
[range_lookup]: Type FALSE for an exact match or TRUE for an approximate match.
Compatibility, Readability, and Performance of HLOOKUP vs VLOOKUP
Some basic differences between these two lookup functions are as follows:
Compatibility
Both HLOOKUP and VLOOKUP are available in all Excel versions, including older ones like Excel 2007 and newer versions such as Excel 365 and Excel 2021. This makes them reliable choices for users working across different versions of Excel.
Formula Readability
In terms of structure, VLOOKUP is often easier to understand because most Excel datasets are arranged vertically, with lookup values in columns. HLOOKUP requires your data to be in a horizontal layout, which can be less intuitive for beginners. Apart from that, both functions share similar syntax and arguments.
Performance
When working with large datasets, both HLOOKUP and VLOOKUP perform at similar speeds. However, VLOOKUP tends to be more efficient in practical use since vertically structured data is more common, and Excel optimizes for this orientation.
Basic Lookups Using HLOOKUP vs VLOOKUP
Let’s look at how both functions work with simple examples so you can clearly see the difference in their lookup direction.
Using VLOOKUP Function
In the following dataset, we have a simple product list stored in Excel. Column A contains the Product Names, Column B shows the Category, Column C lists the Brand, and Column D displays the Price.
We’ll now use the VLOOKUP function to find the price of any product by typing its name in a separate cell.
Steps to use the VLOOKUP function:
➤ Select the cell where you want to display the product price, for example, G2.
➤ Enter the product name you want to search in cell F2. For example, Mouse.
➤ In cell G2, type the following formula:
=VLOOKUP(E2, A2:D9, 4, FALSE)
➤ Press Enter. Excel will return $20, which is the price of Mouse.
➤ Change the product name in F2 to any other item like Laptop to instantly get the corresponding price.
Using the HLOOKUP Function
In the following dataset, we have the same product information arranged horizontally in Excel. The first row contains the Product Names, the second row shows the Category, the third row lists the Brand, and the fourth row displays the Price.
We’ll now use the HLOOKUP function to find the price of a specific product by typing its name in a separate cell.
Steps to use the HLOOKUP function:
➤ Select the cell where you want the result to appear, for example, D18.
➤ In cell C18, type the product name you want to look up. For example, Mouse.
➤ In D18, enter the following formula:
=HLOOKUP(C18, A12:H15, 4, FALSE)
➤ Press Enter. Excel will return $20, which is the price of Mouse.
➤ Change the product name in C18 to any other item like Speaker to view its corresponding price.
Finding Approximate Matches with HLOOKUP vs VLOOKUP
Both HLOOKUP and VLOOKUP can be used to find approximate matches in Excel. This is very useful for finding results within a range, such as assigning grades or calculating commissions based on performance levels.
Using the VLOOKUP Function for Approximate Match
In the dataset below, we have a sales table that shows different Sales Amounts and their corresponding Commission Rates.
We’ll now use the VLOOKUP function to find the commission rate for a salesperson who made $3500 in sales.
Steps to find approximate matches with VLOOKUP:
➤ Select the cell where you want to display the result, for example, E2.
➤ In D2, enter the sales amount value $3,500.
➤ In E2, type the following formula:
=VLOOKUP(C2, A2:B6, 2, TRUE)
➤ Press Enter. The result will be 4% because Excel looks for the closest lower value ($3,000) and returns the corresponding commission rate.
Note:
The first column must be sorted in ascending order for approximate matches to work properly.
Using the HLOOKUP Function for Approximate Match
Now let’s apply the same logic to a horizontally arranged dataset.
Steps to find approximate matches with HLOOKUP:
➤ Select the cell where you want to show the commission rate, for example, C14.
➤ Enter the sales amount $3500 in cell B14.
➤ In C14, type the following formula:
=HLOOKUP(B4, B1:F2, 2, TRUE)
➤ Press Enter. The result will be 4%, which matches the largest value less than or equal to $3,500.
Reverse Search/Last Match Lookup Using the HLOOKUP vs VLOOKUP Functions
A major limitation of both HLOOKUP and VLOOKUP is that they can’t look to the left or above the lookup column or row. These functions can only search in one direction. VLOOKUP scans values to the right of the lookup column, and HLOOKUP scans values below the lookup row.
For example, you cannot use VLOOKUP to find a product name based on the Price if the Price column is to the right of the Name column. Similarly, HLOOKUP can’t return a header or category positioned above the lookup value. This makes reverse lookups or last match searches impossible with these functions alone.
Two-Way Lookup with HLOOKUP vs VLOOKUP Functions
A two-way lookup helps you find a value based on both a row header and a column header. For example, you might want to find the price of a specific product in a particular month. Both HLOOKUP and VLOOKUP can perform this type of lookup by combining them with the MATCH function.
Using the VLOOKUP Function
Here’s a simple dataset containing product prices across different months.
We’ll use VLOOKUP with MATCH to dynamically identify the correct column for the selected month.
Here’s how to do it:
➤ In H2, type the product name Mouse.
➤ In H3, type the month Apr.
➤ Now enter the following formula in I2:
=VLOOKUP(H2, A2:F6, MATCH(H3, A1:F1, 0), FALSE)
➤ Press Enter. Excel will return $26, which is the price of Mouse in April.
Using the HLOOKUP Function
In a horizontally structured table, we can achieve the same result using HLOOKUP with MATCH.
Here’s how to do it:
➤ In H11, type the product name Mouse.
➤ In H12, type the month name Apr.
➤ Then insert the following formula in I11:
=HLOOKUP(H11, A10:F15, MATCH(H12, A10:A15, 0), FALSE)
➤ Press Enter. You’ll get $26, which is the same price located at the intersection of April and Mouse.
Comparing HLOOKUP vs VLOOKUP Function for Error Handling
Both HLOOKUP and VLOOKUP return an #N/A error if the lookup value is not found in the data range. To handle these errors gracefully, you can wrap the functions with IFERROR to display a custom message or a blank cell instead of the error.
➤ Let’s look for the product Smartphone in our dataset and return its price. Since Smartphone is not present, Excel will return an #N/A error. We will replace it with Not Available.
VLOOKUP Function
=IFERROR(VLOOKUP("Smartphone", A2:D9, 4, FALSE), "Not Available")
➤ Replace the lookup value, data range, and column index according to your dataset.
➤ Press Enter to see the result Not Available.
HLOOKUP Function
=IFERROR(HLOOKUP("Smartphone", A12:H15, 4, FALSE), "Not Available")
➤ Adjust the lookup value, range, and row number as required.
➤ Press Enter to display Not Available instead of an error.
Comparison Table of HLOOKUP vs VLOOKUP
Some key comparable aspects between the two lookup functions are as follows:
| Comparable Aspects | HLOOKUP | VLOOKUP |
|---|---|---|
| Available in | All Excel versions | All Excel versions |
| Error Handling | Requires IFERROR | Requires IFERROR |
| Lookup Direction | Vertical (Top to Bottom) | Horizontal (Left to Right) |
| Lookup Flexibility | Can’t look left | Can’t look up |
| Reverse Search (Bottom to Top / Right to Left) | No | No |
| Two-Way Lookup | Yes (with MATCH) | Yes (with MATCH) |
| Speed | Slightly slower for large horizontal data | Slightly faster for vertical data |
| Lookup with Multiple Conditions | Requires helper row or array formula | Requires helper column or array formula |
| Approximate Match | Yes, requires sorted row | Yes, requires sorted column |
| Wildcard Search | Yes | Yes |
| Ease of Use | Easier | Less Common |
Frequently Asked Questions
What is the formula for VLOOKUP and HLOOKUP?
The basic VLOOKUP formula is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The basic HLOOKUP formula is:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Replace the lookup value, table range, and index number according to your dataset. [range_lookup] is optional; use FALSE for exact match and TRUE for approximate match.
When should I use HLOOKUP vs VLOOKUP?
Use VLOOKUP when your data is organized vertically in columns. Use HLOOKUP when your data is organized horizontally in rows. Both are suitable for single-condition lookups.
What is the main difference between VLOOKUP and HLOOKUP?
The main difference is the orientation of the lookup: VLOOKUP searches vertically in the first column, while HLOOKUP searches horizontally in the first row. Other features, like approximate matches and error handling, work similarly.
Which is faster, HLOOKUP or VLOOKUP?
For small datasets, the speed difference is negligible. For large datasets, VLOOKUP is slightly faster because Excel processes column-based vertical lookups more efficiently than horizontal row-based lookups.
Wrapping Up
HLOOKUP and VLOOKUP are essential functions in Excel for quickly finding data in tables. VLOOKUP works best with column-based datasets, and HLOOKUP is ideal for row-based layouts.
Understanding the structure of your dataset and the type of lookup you need will help you choose the right function. Mastering these functions can save time, reduce errors, and make your Excel analysis more efficient.
