Pulling specific data from a dataset with thousands of cells is never easy. Lookup functions make this effort easier with their miraculous features. HLOOKUP and VLOOKUP are two of the most popular lookup functions for this piece of work. Our objective for this article is to present everything about these two functions in an HLOOKUP vs VLOOKUP approach.
Lookup functions in Excel are constant lifesavers for a user. They do complex searches for specific data. HLOOKUP and VLOOKUP are two functions that belong to the lookup category. HLOOKUP searches for data horizontally in a single row. On the other hand, VLOOKUP seeks specific data in a vertical direction. We intend to explore both HLOOKUP and VLOOKUP in this article.
Our approach would be to put everything on the table about these two functions in brief. We will discuss the limitations and alternatives of these functions here as well. And in the later part of the article, we will also include a comparison table to help you further understand the similarities and differences between HLOOKUP and VLOOKUP.
Let’s commence!
Contents
HLOOKUP vs VLOOKUP: A Quick Look
Excel’s Lookup & Reference category in the Formulas tab houses more than 30 convenient functions. The HLOOKUP and VLOOKUP functions belong to this category. Both these functions help find a specific value against a mentioned value from large datasets.
Finding the correct information is more challenging than can be said. We can compare it to finding a needle in a haystack. It usually feels hopeless when you want to find a value based on your given criterion from a dataset with thousands of data cells.
HLOOKUP and VLOOKUP guide you to the correct cell and pull the accurate data. HLOOKUP does a horizontal search for your given criterion. When it locates the match, it then goes in the down direction to find the data you are looking for.
On the contrary, VLOOKUP does a vertical search for the mentioned value in a dataset. Upon finding the correct match, it then goes toward the right to locate the data you are seeking. In respective sections, we will discuss how both HLOOKUP and VLOOKUP work in detail.
Before we get into the face-to-face battle of HLOOKUP and VLOOKUP, let’s examine both functions with their syntax explanation and mechanism. We will also show a quick use of these functions afterward.
The HLOOKUP Function: A Discussion
The HLOOKUP is one of the Lookup functions in Excel. The “H” in HLOOKUP stands for “Horizontal”. This function is suitable when you have a horizontal dataset and need to find a specific value against the lookup value you will provide.
When a formula is used for this function, Excel searches out the lookup value at the top of the selected range. When it finds a value that matches the input, it then looks in that column to find the wanted data. We will explain the process in depth.
Now let’s see the formula breakdown for this function.
HLOOKUP: The Syntax Breakdown
The formula for the HLOOKUP function has four arguments: the first three arguments are mandatory, and the last one is optional. By understanding the formula’s structure, you will quickly comprehend how to work with it.
The HLOOKUP formula is: =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]).
Here,
- HLOOKUP : The function.
- lookup_value : The first mandatory argument. The value or criterion the HLOOKUP will look for across the top row of your selected range of data.
- table_array : The second mandatory argument. It is your selected range of cells where the lookup value will be searched for.
- row_index_num : The third mandatory argument. It is an nth value within your table_array. The row index number refers to the row number in your range of selection from where the value will be returned. After HLOOKUP finds a match of the provided lookup_value in the topmost row of your selection range or array, it will go down to the row number mentioned in this argument.
- [range_lookup] : An optional argument. It is a Boolean value where either TRUE or FALSE is used to find the approximate match. Using TRUE or not mentioning anything for this argument will make HLOOKUP find approximate data against your criteria. And if you use FALSE, an exact match will be returned.
If you try to insert the HLOOKUP formula using the Insert Function option in the Formulas ribbon, you will get a Function Arguments window like the one below.
Suppose you are new to Excel and uncomfortable writing the formula yourself. In that case, you can use this window to input your arguments.
The Mechanism of HLOOKUP
We will show how the HLOOKUP function works in this part of the article. We have created sample data for demonstrations here. The data is small for a more straightforward explanation. However, in real situations, you will need to use HLOOKUP to find values from massive datasets.
When you write the formula for HLOOKUP, it looks similar to the picture below.
As you see, four arguments work together to make the HLOOKUP work. It works in a horizontal direction. Take the image here as an example.
When an HLOOKUP is initiated, the function works in four steps.
1. The HLOOKUP takes the lookup_value and checks if it is valid. It can be a text string, reference, or numeric value.
2. In the second step, HLOOKUP takes the range of selection or table_array.
3. In the third step, HLOOKUP starts searching for a match of the lookup_value entered in the argument. It goes straight to the top row of the selection and goes from the leftmost cell in the row to the right.
4. In the fourth and final step, provided that HLOOKUP has found a match in the top row, it stops its horizontal search in that cell. Then it takes the row_index_num value and goes towards the bottom from the top row to find that nth row. Upon reaching the location, HLOOKUP returns the value to the destination cell.
Important Notes: Keep some things in mind before using the HLOOKUP function.
1. In the selected range, the lookup_value must be in the first/topmost row. Otherwise, the function will not work. For instance, if your lookup value is in the second row of the selection, you will get an error. The INDEX+MATCH combination of functions remedies this problem which we will discuss in the alternatives section.
2. If you intend to find a series of data from the same dataset, do not forget to use Absolute Reference to lock out your table_array selected range in the formula. Otherwise, the selection will move when you fill cells with the formula.
3. The lookup value is NOT case-sensitive. For instance, if your table has “Lucas” in the top row, but you write “LUCAS” or “LuCaS” in the lookup_value, the HLOOKUP will still work.
4. You will get an error if any mismatch happens or HLOOKUP does not find the corresponding value. We will discuss all the errors you may get while using HLOOKUP in the Troubleshooting section of this article.
HLOOKUP in Action
Now that you know how HLOOKUP works during an operation, you may find it easier to use this function. Nevertheless, we will show a quick example of HLOOKUP in this section.
Suppose we have a dataset with employee information like the one in the picture. If you notice, some employee names are in a row below the data table. We will use the names as the lookup_value for HLOOKUP to find those employees’ phone numbers.
Just below the name, we wrote the HLOOKUP formula. The whole table was selected as the table_array argument. And we made sure to use the Absolute Reference in the table_array argument. We pressed the F4 key once right after selecting the range.
Our selection had four rows. The topmost row had the lookup_value. And since we intended to find the corresponding phone number for the name, we could see that the nth value for the row_index_num regarding the phone number was “3”. Because the phone number data was in the third row of the selection.
Upon pressing Enter, we got the data we were looking for.
Then we filled up other cells with the formula to find corresponding phone numbers.
Suppose we were to find the Email addresses of the employees instead of phone numbers. We simply changed the nth value of the row_index_num to “4” because the fourth row in the selection had email addresses.
Without any problem, we got the employees’ email addresses as well!
How fun was that?
The VLOOKUP Function: A Discussion
The VLOOKUP function is one of the three most used functions in Excel. It has been with Excel since the beginning. Users consider VLOOKUP to be the ultimate master of LOOKUP operations. The “V” in the name stands for “Vertical”.
If you have understood HLOOKUP from our discussion before coming to this section, you will grasp everything about VLOOKUP in no time! You have seen that in HLOOKUP, the function searches for a match of the lookup value in a horizontal direction in the topmost row.
In VLOOKUP, the direction changes. The function goes to the first column of the selected range or table_array, and looks for the matching value from the top to the bottom direction. Once it finds the match, it then goes towards the right to the nth value mentioned in the argument and returns the sought value.
Let’s understand this VLOOKUP part by part. First, we will do a syntax breakdown. Then we will discuss how this VLOOKUP works with an extensive explanation. Finally, with sample data, we will show an application of VLOOKUP.
VLOOKUP: The Syntax Breakdown
The VLOOKUP function has four arguments: three are mandatory, and one is optional. It is similar to what you have seen in the case of the HLOOKUP function. The only difference is in the third argument, which we will discuss shortly.
The VLOOKUP formula is: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]).
Here,
- VLOOKUP : The function.
- lookup_value : It refers to the criterion or the value which will be looked for in the first column of the selected range or table_array. It is a mandatory argument.
- table_array : The range of selection where the lookup_value will be searched, and the corresponding value from the nth index will be returned. This argument is mandatory in the VLOOKUP formula.
- col_index_num : The nth value in your selection range for the table_array argument. It is the third and final mandatory argument in this formula. The VLOOKUP looks for a match of the lookup_value in the first column of the table_array. Afterward, it goes to the mentioned nth value in this argument and returns the value from that cell.
- [range_lookup] : The Boolean value for finding either an exact or an approximate match. It is an optional argument. It uses TRUE or FALSE. The TRUE value is the default one here. If you use this value, the VLOOKUP will give you an approximate match. On the other hand, if FALSE is used, you will get the exact match for your data.
The image below shows a window titled Function Arguments for the VLOOKUP.
You can get this window and input your arguments if you don’t want to write formulas manually. To open this window, go to the Formulas ribbon in your Excel program. Then you either use the Insert Function option or find and select VLOOKUP from the Lookup & Reference option.
The Mechanism of VLOOKUP
The VLOOKUP function works in four simple steps. But before talking about it, we want to draw your attention to the image below. It shows the arguments of the VLOOKUP formula when a user writes it in a cell.
You can see the four arguments of the VLOOKUP here, which we discussed in the previous section. Now, the function considers every argument during an operation. It works in four steps by following a vertical direction to search the lookup value. The picture below shows the directions regarding how it works.
The VLOOKUP completes its job in four steps.
1. In the first step, the VLOOKUP takes the input lookup_value and checks its validity.
2. The function then follows the argument for the table_array and marks the range of selection for the operation.
3. In the third step, VLOOKUP goes to the first column of the selection. Then it searches for a match of the lookup_value in a top-to-bottom direction.
4. Once it finds a match, VLOOKUP moves horizontally in the right direction. It goes to the nth value mentioned in the col_index_num argument and returns the value it finds there.
Important Notes: The things to keep in mind while using the VLOOKUP are as follows:
- Always make sure to use the Absolute Reference for your range of selections.
- Ensure that your selected range has the lookup_value in the first column. Else, you will get a #N/A error.
- The lookup_value is NOT case-sensitive.
VLOOKUP in Action
Now we will show a simple VLOOKUP operation with a small dataset. It is a snippet of VLOOKUP’s effectiveness when finding specific information from a large dataset.
In the image below, you can see on the right a small section where we wanted to find the phone numbers of employees. We used the names as lookup_value in this operation.
We wrote the VLOOKUP formula. As we did in HLOOKUP’s case, we used Absolute Reference here, too, for the table_array or the range of selection.
The first column in our selected range had the names. VLOOKUP went through those names to find a match. Then, within the selected range, it used the col_index_num argument “3” from the formula input as the nth value for the location of the phone number.
Like nothing less than magic, we got the employee’s phone number in the destination cell!
We filled the column with the formula to find the phone numbers of other employees on the list.
As a different example, we wanted to find each employee’s department on the list. We simply changed the col_index_num value in the formula and put “2” as the nth value. The reason was that, as you can see in the selected range, the 2nd column had the Department data.
As expected, we got the departments for all the employees on our list.
Amazing, isn’t it?
You can find an entire article on VLOOKUP with some excellent uses on our website. Check it out if you are looking to learn everything about this function.
Limitations of HLOOKUP and VLOOKUP
HLOOKUP and VLOOKUP are two of the most impressive functions in Excel. The amount of work they cut down with their effective data lookup operations is immeasurable. However, there are some common limitations of HLOOKUP and VLOOKUP. In this section, we will discuss them in brief.
1. Single Lookup Value: HLOOKUP and VLOOKUP are limited to working with one lookup_value for one formula instance. If you need to get results for multiple lookup_value, you are out of luck!
2. One-Dimensional Search: HLOOKUP and VLOOKUP cannot find more than one result for a single lookup_value. HLOOKUP will search for the lookup_value in the first row and then go to the nth row to pick the output. Similarly, VLOOKUP will seek the lookup_value in the first column and then go toward the right to the nth column to get the output. In both cases, the operation stops after finding the data and cannot look for more results with the same lookup_value.
3. Not Going Beyond the First Match: Let’s say your dataset has more than one match for the lookup_value. HLOOKUP and VLOOKUP stop the search upon finding the first match and return the corresponding output. If your intended output is with a different lookup match, you will get a wrong result due to this limitation.
4. Lookup Location: By default, HLOOKUP and VLOOKUP can look for the lookup_value in the topmost row and the leftmost column, respectively. If you select a range data for your table_array where the first row (for HLOOKUP) or first column (VLOOKUP) does not have the lookup_value, you will get a #N/A error straightaway. Therefore, you will not get an output if your lookup_value is in a row below the topmost row (for HLOOKUP) or to the right of the leftmost column (for VLOOKUP).
5. Case Insensitivity: Both HLOOKUP and VLOOKUP are not case-sensitive. The exact match argument does not work in this instance. Suppose, for VLOOKUP, you have data with “Carol” first and “carol” down the column. Now you want to get the output for “carol” (which comes later in the column) as the lookup_value. If you do a VLOOKUP, you will get a result for the “Carol” match because the function is not case-sensitive. Thus, you will be getting the wrong output for your lookup. It is the same for HLOOKUP as well.
6. Restriction of Row and Column Alteration: If you want to move, insert, or delete a row (for HLOOKUP) or column (for VLOOKUP) in your dataset, the HLOOKUP and VLOOKUP will start giving you the wrong output. Suppose you have selected columns C to F for your table_array where, in column D, you have your output data to be picked. You put “2” as the nth value for your col_index_num. Now if you add a new column between columns C and D, the new column will be the output source for your VLOOKUP. You will get the wrong data for your lookup_value.
7. Sorted Data Requirement (VLOOKUP): The VLOOKUP returns incorrect outputs if the first column of the selected range of data is not in ascending order. On the other hand, HLOOKUP does not have this complication.
Alternatives of HLOOKUP and VLOOKUP
The limitations of HLOOKUP and VLOOKUP may sometimes hamper your work. Hence, to take some pain away, we consider it our responsibility to discuss alternatives to these functions.
Alternative 1: The XLOOKUP Function
In recent years, Microsoft introduced the XLOOKUP function to Excel. At first, it was added to the Excel for Microsoft 365 version. Then it became available in Excel 2021 version as well. This function removed the limitation of a single selection range or a fixed first lookup location.
You saw in both HLOOKUP and VLOOKUP how these functions do a one-dimensional search with a specific requirement. You need to do one range of selection where the first row (for HLOOKUP) or the first column (for VLOOKUP) has to have the lookup value. Then, for the intended data, an nth value is used. But the XLOOKUP removes the whole limitation.
1. XLOOKUP uses two different arrays for the lookup array and return array. There is no use for a single array anymore. As a result, your lookup array can be to the right or left of your return array, and the function will work fine!
2. There is no need to sort your data before using the XLOOKUP. It was a mandatory action before using the VLOOKUP.
3. XLOOKUP can perform both horizontal and vertical lookups. In a way, it is both HLOOKUP and VLOOKUP within one function on steroids!
There are more advantages to using XLOOKUP instead of HLOOKUP and VLOOKUP. We have a dedicated article on this XLOOKUP beauty. Don’t forget to check it out!
Alternative 2: The INDEX(MATCH) Combination
The INDEX+MATCH combination has been the problem solver of HLOOKUP and VLOOKUP for many years. This combo eliminated many limitations. Even after the introduction of XLOOKUP, the INDEX(MATCH) remains one of the most extensive and advanced lookup techniques.
Individually, the INDEX and MATCH functions give pretty basic results. However, when the MATCH is nested within the INDEX function, the level of the game changes.
Some of the advantages of the INDEX(MATCH) are listed below.
1. INDEX(MATCH) can do a case-sensitive lookup. This combination of functions will give you the exact result for the specified lookup value. It was a significant limitation of HLOOKUP and VLOOKUP.
2. Even on a single range of selection, INDEX(MATCH) can find you the right output irrespective of the direction of the return array. In HLOOKUP and VLOOKUP, the row or column for the lookup has to be the first one. INDEX(MATCH) changes the play here.
3. INDEX(MATCH) combo can give you a range of return output or array. Moreover, you can do a lookup with multiple lookup_value in one formula instance!
4. The INDEX(MATCH) combination can do a double-way lookup. It does not have the limitation of being a one-dimensional lookup operation. In HLOOKUP, it can search out the lookup value in a row. And in VLOOKUP, the search for the lookup value takes place in a column. The INDEX(MATCH) can search for both directions simultaneously.
As you can see, the INDEX(MATCH) has a robust lookup method. If XLOOKUP does not work in your Excel due to compatibility issues with the versions of Excel, you can easily tackle your HLOOKUP and VLOOKUP problems with this INDEX(MATCH) technique.
There are more alternatives to HLOOKUP and VLOOKUP, like CHOOSE(MATCH) and OFFSET(MATCH). However, the XLOOKUP and INDEX(MATCH) are powerful enough to work without the limitations HLOOKUP and VLOOKUP comes with. Thus, if you are looking for alternatives, our suggestion would be XLOOKUP and INDEX(MATCH) without a shadow of a doubt!
Troubleshooting HLOOKUP and VLOOKUP
When you use HLOOKUP and VLOOKUP in your daily job, you will most likely get some errors in the output. There are usually five types of errors you can see for these two functions. In brief, we will now discuss these error messages, the reasons behind them, and how to eliminate those errors.
1. #N/A : You see this error in the output when the lookup value is not present in the first row (for HLOOKUP) or the first column (for VLOOKUP) of your selected range or table_array. It is the most common error an Excel user gets when they use HLOOKUP and VLOOKUP. Ensure that your input lookup_value is in your selection’s first row or column.
2. #VALUE! : This error makes an entry when an argument in the formula of HLOOKUP or VLOOKUP is invalid. For instance, if you use “0” or a negative value for the row_index_num or col_index_num argument in HLOOKUP and VLOOKUP, respectively, you are bound to get this error. Stay alert when you enter the arguments for your formula. And when you get this error, examine your formula, see where the discrepancy is, and make the necessary correction.
3. #REF! : When the nth value for the row_index_num or column_index_num is greater than the number of rows or columns in the selection, the #REF! error shows up. For instance, in VLOOKUP, if your selected range for the table_array has four columns, but you put “5” in the column index number, you will get this error. It also occurs when the table_array reference is invalid.
4. #NUM! : If the row_index_num or column_index_num does not contain a valid numeric value, you will get this error. Also, if the range_lookup argument has an invalid input, this #NUM! error shows up.
5. #NAME? : If you misspelled the function name in your formula, you will see this error instantly. Double-click on your formula and check the function name if you see this error in your output.
These are the five errors you will likely get in your everyday use of HLOOKUP and VLOOKUP.
HLOOKUP vs VLOOKUP: A Quick H2H Battle
So far, we have discussed everything about HLOOKUP and VLOOKUP as briefly as possible. However, if you still want a direct comparison between these two functions, here is a quick H2H table for your convenience.
| HLOOKUP | Specification | VLOOKUP |
| Horizontal
Useful for searching for information in a row |
Lookup Direction | Vertical
Useful for searching for information in a column |
| The first row of the selection | Lookup Location | The first column of the selection |
| Can search for the lookup_value in one row | Limited Search | Can search for the lookup_value in one column |
| Optional | Sorted Data Requirement | Mandatory |
| Not available | Multiple Lookup | Not available |
| Not available | Retrieving Multiple Return Value | Not available |
| Lookup_value
Table_array Row_index_num Range_lookup |
Function Arguments | Lookup_value
Table_array Column_index_num Range_lookup |
Concluding Thoughts
Finding the right information from large data is never easy. HLOOKUP and VLOOKUP help massively with this task. They allow users to locate the correct data by comparing it against a given lookup value. In this article, we discussed both HLOOKUP and VLOOKUP in an extensively comparative manner.
The HLOOKUP function in Excel is an advanced lookup function that works in a horizontal direction. It can search for values in rows only. Contrarily, the VLOOKUP is a lookup function that does a vertical search for the mentioned value. It can seek data in columns. The VLOOKUP has been the most used lookup function for over three decades.
Nevertheless, Microsoft introduced XLOOKUP as an alternative that has no directional limitations. We discussed it in brief in the Alternatives section of this article. XLOOKUP eliminates the directional lookup restriction both HLOOKUP and VLOOKUP have in their operations.
Although this is a comparison article on HLOOKUP vs VLOOKUP, our approach was to show you the definitive sides of these functions in a broader comparison. The goal was to help you understand HLOOKUP and VLOOKUP from every angle. After knowing about both functions, you will perceive the differences more effectively and determine the function to use for a specific requirement.
Similar Post: