HLOOKUP Function
A Microsoft Excel function used to retrieve information from a data set using a horizontal row as a parameter.
What is the HLOOKUP Function?
The Horizontal Lookup Function (commonly known as the HLOOKUP function) is a Microsoft Excel function used to retrieve information from a data set using a horizontal row as a parameter.
Initially, there were two main lookup functions, VLOOKUP, which searches a column and provides a value from a corresponding row, and HLOOKUP, which searches a row and returns a value from a corresponding column.
However, in 2019 when Microsoft unveiled its MS Excel update, it introduced the new XLOOKUP function. XLOOKUP can do the same functions as its lookup brothers but uses more parameters, confusing newer users.
Lookup functions are vital to data management and analysis. If you are only working with small amounts of data, it is easy to quickly examine exactly what you want. However, lookup functions shine when you need to work with mountains of data in Excel.
Let’s look at how we can use the magic of lookup functions to “excel” in our skills!
Key Takeaways
- HLOOKUP is useful when working with data where the lookup values are on the first ROW of the table.
- Just because your function returns a value does not mean you have the right answer. Double-check everything, always.
- Use wildcards to mitigate using longer strings. The longer your entries, the higher the chances you have an error.
- If you have duplicate lookup values, you will only have the answer for the FIRST look-up.
Formula for HLOOKUP
In Excel, a formula refers to a predetermined function that follows a set of logic based on certain parameters called criteria, conditions, or arguments. All functions in Excel start with “=.”
The HLOOKUP function is:
Let’s break down what the arguments are:
- Value: This is a required argument. An argument for specifying the value we are looking for. It can be numerical, alphabetical, or even a reference.
- Table Array: This is a required argument. A table of information from which the function can “look up.” Ideally includes your whole data set.
- Row Index Number: This is a required argument. It specifies which row the functions should take to return the solution corresponding to the initially established lookup value.
- Range Lookup: This is an optional argument. This specifies whether or not the function should return an EXACT match or an APPROXIMATE match. It takes the arguments TRUE or FALSE.
How to use the HLOOKUP function
To use the Horizontal Lookup function, we need to start with a data table, commonly called an array. Let’s use the one below as an example.
The table gives us the grades of 5 students, whose names are given on the horizontal axis, and their respective grades in 6 subjects. Now assume we need to pull the information of just an individual student.
Step 1: Type an “=” and the function name and open parenthesis.
Step 2: Determine which student’s grades to focus on. For this example, let’s use John. This is our “Lookup_Value.” You can directly click on John’s name.
Step 3: Select the whole array of data.
Step 4: Figure out the “row_index_number. This is the row number from which the information should be pulled. For our example, let’s try and get John’s Mathematics grade. This is the 3rd row in our array, making our index number “3.”
Step 5: Since we are looking for an exact match, our final entry is FALSE.
Step 6: Hit Enter and let that Excel do its magic! And BOOM! There you go. It looks like John needs to work on his math skills. Maybe he can practice math in one of WSO’s Financial Modelling Courses!
HLOOKUP Function: Things to remember
There are a few important details to remember while working with lookup functions. These nuances are important to remember because it may be hard to determine if you have the wrong value when working with larger data sets, unlike our example.
- Remember that the lookup value is NOT case-sensitive. Both strings “Dan” and “dan” will be treated the same.
- The lookup value can not exceed 255 characters.
- HLOOKUP only works if there are no duplicate lookup values in the array. If there are duplicate values, only the first value will be returned.
- Wildcard arguments (*) and (?) are usable in lookup functions.
- The lookup value needs to be at the top of the array.
There are a few cases in which your equation may return an error. First, ensure there are no extra spaces in your equation. Then, you can use the TRIM equation to ensure your equation is compact.
A helpful tip might be to use Ctrl+Enter while submitting your functions to enclose them in curly (“{}”) brackets.
Lookup Functions - Key Differences
The three main lookup functions serve similar purposes to HLOOKUP, but all serve different functions.
VLOOKUP completely mirrors HLOOKUP. When you would choose either depends on how your array is set up. If your lookup value is on your vertical axis (the leftmost column), VLOOKUP is the way to go.
XLOOKUP is an alternative to the other two lookup functions. However, it has quickly replaced them since it can search for data horizontally and vertically. This is because it uses extra parameters to determine how to return a value.
XLOOKUP can also use various methods to search for your data. Remember how I mentioned the other functions only return the first value it finds? XLOOKUP can search reverse or return whole columns and rows.
Lookup with other functions
You can use your lookup and other functions to manipulate your data set. Let’s use the data set below for our example.
Assume the table shows various payments made by different customers for Imaginary Company LLC. You are trying to figure out customers that spend the most at your establishment so you can better focus your resources when they return.
Let’s try using a lookup function combined with an IF function to return a result that is easier to comprehend. For example, assume that customers who spend over $3500 are our focus, and we label them “High-Spend " or “Low-Spend.”
Combining the IF function with the HLOOKUP function makes our life easier in not only pulling out data but also presenting it in a way that is easy to understand. The best way to learn is to DO! Try recreating the equation for Adam. You already know the answer; see if you can get it right!
We used a simple instance as an example, but by now, I am sure you have seen the power of HLOOKUP.
HLOOKUP Function: Common Errors
You finally took on the challenge of using a lookup function head-on, but now the function returns an error. So let’s try and figure out how we can fix this!
Remember the previous example; let’s figure out the error here.
The No value available error (#N/A) occurs because there is no Daniel in our primary data set. If you are certain that a lookup value exists for your array, ensure you have entered the formula correctly.
Another reason for an #N/A Error may be that you copy-paste the formula for your next customer without considering your absolute reference. Absolute Referencing uses the “$” (shortcut F4) sign to lock certain cells in place when copying formulas.
Replace #N/A Errors with your text to maintain professionalism in your Excel sheets.
Let’s take a look at another common error. First, focus on the formula bar to see if you can figure out the mistake.
Does the function return the #NAME? Error due to a spelling error when it was written. Try and make it a habit to use TAB on your keyboard when function name suggestions appear to try and avoid this error.
Another error may occur because your row-index-number changed due to adding a new row. You must be careful when working with large data sets because your lookup value may change without notice.
Free Resources
To continue learning and advancing your career, check out these additional helpful WSO resources:
or Want to Sign up with your social account?