Excel's HLOOKUP function is a valuable tool for extracting data. However, it's often overlooked due to unfavorable comparisons to VLOOKUP and XLOOKUP. While it is similar in structure and uses to VLOOKUP and XLOOKUP, it has a place when you need to analyze data arranged in a particular way.
With HLOOKUP, you can analyze horizontal data without transposing it. This feature makes it a helpful tool, and you should include it in your arsenal of LOOKUP functions.
Excel's HLOOKUP Arguments
Excel's HLOOKUP function consists of four arguments. However, the fourth argument is optional. These are the arguments and what they do:
- lookup_value: This is the value you want the function to find in the first row of your data array. It can be a numerical value, a reference to another cell, or a string value.
- table_array: This refers to the set of cells you want to search for. The first row of this array is where HLOOKUP will search for a match for the first argument. The rest represents the data you want to include with your results.
- row_index_num: This row contains the value you want to return.
- range_lookup (optional): This argument can either be TRUE or FALSE. If TRUE (or excluded), HLOOKUP will return the column with the closest match. But if it's FALSE, it will only return exact matches.
The function may return unexpected results if the range_lookup argument is TRUE or not included. You will most likely want to set this argument to FALSE to avoid false matches. The same error is a common issue with the VLOOKUP function as well.
Furthermore, if you set range_lookup to TRUE or exclude it, you must sort the first row of your data alphabetically or in ascending order.
How Are Excel's HLOOKUP, VLOOKUP, and XLOOKUP Different?
Excel's HLOOKUP is highly similar in structure and use to two of Excel's other lookup functions, VLOOKUP and XLOOKUP.
The primary difference between HLOOKUP and VLOOKUP is the orientation of the data searched. VLOOKUP assumes that the data you examined is organized horizontally, where each row in the table represents a record.
HLOOKUP, on the other hand, assumes that your data is arranged vertically. Each column represents a separate record, and fields on the records are organized by row.
XLOOKUP is Excel's newest LOOKUP function. Although it's similar to VLOOKUP, it can analyze both vertical and horizontal data. It can also return an array of cells based on the match's location instead of the single-cell reference limitation of HLOOKUP and VLOOKUP.
Overall, these differences limit the use cases for HLOOKUP somewhat. In most situations, data will be arranged horizontally, which makes VLOOKUP and XLOOKUP much more functional.
Examples of Excel's HLOOKUP
To show you how HLOOKUP works, let's look at an example:
Here we have a basic sales ledger arranged horizontally. The sheet shows the transaction ID, transaction date, transaction amount, and a unique customer ID.
Below the records, we have an entry for the transaction ID. When you type in a valid transaction ID, the transaction amount and customer ID are pulled from the table above.
In cell B7, you can use the following function to get the amount of the transaction:
=HLOOKUP(B6, B1:H4, 3, FALSE) In cell B8, you can use the following function to get the Customer ID:
=HLOOKUP(B6, B1:H4, 4, FALSE) In the first function, we set row_index_num to 3. This means it will pull the third row in the column that matches the value entered as the first argument, which points to B6. In the second function, the third argument is instead swapped out for 4, which allows it to pull the fourth row instead.
In both cases, the final argument, FALSE, is included. This means that the HLOOKUP function will only accept an exact match. If the transaction number entered isn't in the table, both functions will result in an error.
The simplest way to avoid this issue is to wrap both functions in an IF function and use the ISERROR function to determine if the function is valid. Wrapping the function allows you to enter a default value that will be shown if the transaction ID isn't located in the table.
Now the two functions in B7 and B8 are as follows:
=IF(ISERROR(HLOOKUP(B6, B1:H4, 3, FALSE)), "Transaction Not Found", HLOOKUP(B6, B1:H4, 3, FALSE)
=IF(ISERROR(HLOOKUP(B6, B1:H4, 4, FALSE)), "Transaction Not Found", HLOOKUP(B6, B1:H4, 4, FALSE)) Using IF and ISERROR allows the spreadsheet to offer a helpful message if the transaction ID specified isn't found. These two functions are one of the simplest fixes you can use in Excel when your formulas may result in an error.
If you remove the final argument from both statements in this case, you can see what happens when you don't limit the search to exact matches:
In the above example, even though transaction ID 445880 does not exist, an amount and customer ID are still returned. Without the last argument set to false, the search used will instead return the next closest match that doesn't exceed the value entered.
In this case, the functions pulled the amount and ID for transaction 445879 since it was the closest match.
When the non-exact search is used, the closest match cannot exceed the searched value. This means that if the transaction ID entered is lower than any transaction in the table, no transaction will be found.
Since it is optional, leaving the final argument empty is an error many users make. As such, the last argument should always be listed as false unless you are certain that your situation calls for non-exact matches.
Why You Should Know and Use HLOOKUP
HLOOKUP is one of the least used functions in Excel's LOOKUP function family. Compared to XLOOKUP and VLOOKUP, the HLOOKUP function requires data to be arranged in a particular and atypical way.
The specific data requirements, combined with the confusion of the fourth argument, often mean that HLOOKUP is undervalued. It is, however, the ideal tool under certain circumstances—it can help you pull much-needed data from inopportunely arranged data sets where neither of its siblings would function.
By knowing HLOOKUP, you can save time and effort, making it a valuable addition to your Excel function toolkit—even if it isn't commonly used.
ncG1vNJzZmivp6x7rq3KnqysnZ%2Bbe6S7zGiuoZmkYra0ecSxmp6kXZ25sLvKrqdmnqWjsLW1zqdkoaenYsGwedSsnGahpGQ%3D