The Horizontal lookup function, HLOOKUP in excel is used to find the specific value in a row for the table that is arranged horizontally.
On the other hand, if table data is arranged vertically then we use VLOOKUP (vertical lookup) function.
In the HLOOKUP function, lookup value is provided that is searched in a table or array of values and it returns a value in the same column from a specified row. That specified row must be in the table or array. Still unclear? Have a look at the few examples that should make things clear.
Syntax of HLOOKUP function
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Before explaining these arguments, let us look at HLOOKUP function in action that will make you easily understand that.
The simple example of Excel VLOOKUP
Our example sheet contains Names of students, scores in three subjects and their total. The demo data is arranged horizontally as shown below:
Now suppose, we want to retrieve the total marks of “Jhony”. See how we can do this by using the HLOOKUP function:
The HLOOKUP formula:
The resultant sheet:
Now, let me explain the arguments of HLOOKUP function on the basis of this example.
- The lookup_value is required. In the example, the Total is the lookup_value. This is the value to be found in the first row of the table. In our example, the first row contains the headers and “Total” is one of those. You may also use cell reference there.
- The second argument is the table_array which is also required. This specifies the area in which lookup_value is required to be found. In our example, A1:E5 is the table_array. You may also use the range name there.
- Third argument is row_index_num, required as well. This is the number of row in the table_array. Do not mix it up with the actual row number in the excel table. As such, your table_array may be the subset of the excel table. In our example case, we used 5 value that is related to the “Johny” record.
- The final argument is range_lookup and this is optional. But you should generally use it. It defaults to True; that means find the approximate match. The FALSE logical value means find the exact match. I will explain this further in the coming section with an example, especially the TRUE value.
- The HLOOKUP function is case-insensitive. That means, “Bio” and “bio” is taken as same.
The example of using cell reference in HLOOKUP Excel function
Rather than using the hard code value as used in the previous example “Total”, we may use a cell reference there as well. That approach enables us using the lookup_up value dynamically.
In the context of our example, this can be useful for not only finding the Total but individual subject marks as well. For example, I want to search the marks in Chemistry, and Biology along with the total for Haynes. See the formula and sheet for an idea how you can do this:
The HLOOKUP formula:
The results as I entered different texts in cell C8:
Using the wildcard example in HLOOKUP function
You may also use the wildcards in HLOOKUP function. The supported wildcard characters are:
- “*” The asterisk matches any sequence of characters.
- “?” For matching the single character
To demonstrate that, I am moving ahead with the above example. I will just enter the “Phy*” and “Che*” and see how “*” wildcard works in HLOOKUP:
Note that, the same formula is used as in above example. We just entered the wildcard in C8 cell.
The example of ‘?’ wildcard
I used Bio???? and see the output:
If I entered Bio??, an error would have produced.
The #NA error and resolving this in HLOOKUP
The HLOOKUP function produces an error if a given search term is not found in the table_array. For example, I entered “Math” in our example sheet and see the output:
You may display a descriptive message by using IFERROR function with HLOOKUP as follows:
The IFERROR/HLOOKUP formula:
1 =IFERROR(HLOOKUP(C8,A1:E5,2,FALSE), "Please enter available subjects")
You see, now it displayed “Please enter available subjects!” as I entered Math.
Using True value for Range_Lookup argument example
Just to remind, using TRUE value means to return the approximate match. If you are using the TRUE value then make sure that values in the first row of table_array must be in ascending order. Otherwise, it may return incorrect results.
Note: TRUE is the default value. If you omit this, the VLOOKUP function will use TRUE.
First, have a look at the example of using the same order as in above examples with TRUE value:
You can match the returned result which is incorrect. It displayed the marks of Biology while I entered Physics.
See the outputs as I re-ordered the subjects in ascending order and changed the range a bit:
Generally, you will use the exact match option by using FALSE value in the HLOOKUP formula.