Mastering the HLOOKUP function in Microsoft Excel. This tutorial will show you to the syntax of HLOOKUP in Microsoft Excel and helped you understand how to use them for your data using multiple examples.
Knowing enough about Microsoft Excel has become an essential skill for beginner data analysts due to its widespread use in the industry and its user-friendly interface.
Despite the realization of the need for complex data architectures and big data analytics tools to tackle the amount of big data collected, processed, and analyzed, many organizations still rely on Excel for data storage, manipulation, and analysis, making it one of the most commonly used tools for analytics purposes.
It’s rare not to see an organization using Microsoft Excel for a quick ad-hoc analysis on a snapshot of the data. Thus, data analysts must be equipped with Excel skills, especially built-in functions such as pivot tables, conditional formatting, lookup functions, and more.
After all, it’s quite easy to learn in a relatively short time without the need for advanced programming skills.
This tutorial will introduce you to one such built-in function — HLOOKUP. I’ll provide you with the syntax and help familiarize you with its usage with several examples. With multiple instances, including screenshots and frequently asked questions, this tutorial will be everything you need to start using HLOOKUP in Excel.
HLOOKUP (Horizontal Lookup) in Excel is a built-in function that enables users to find data in tables horizontally. It searches for a specific value in a table’s top row and returns a value from a row specified in the table or array.
HLOOKUP is useful in scenarios where you need to retrieve information from a data set that is arranged in rows rather than columns.
It is particularly handy when dealing with data tables where the key values are spread across the top row, and you need to extract corresponding values from lower rows based on a horizontal match.
The syntax for HLOOKUP in Excel is as follows:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
OpenAI
The parameters of the function are:
It’s important to note that if the [range_lookup] parameter is set to TRUE or omitted and an exact match is not found, the function will return the value corresponding to the largest value that is less than or equal to the lookup_value, assuming the first row of the table_array is sorted in ascending order.
If the [range_lookup] parameter is set to FALSE, and an exact match is not found, the function will return an error.
Now that we have learned the syntax of HLOOKUP — let’s practice it using a few probable business scenarios as examples.
Consider a small online retail company that sells various electronic products. The company tracks its monthly sales data in an Excel spreadsheet. The management team wants to quickly retrieve the sales figures for specific products in different months to analyze trends and make informed decisions.
A snapshot of the relevant data is available in an Excel sheet:
Online retail company sales data. (This and all below images by author)
We’ll see how to use the HLOOKUP function to find the sales figures for “Laptops” in March:
Selecting a cell to perform the HLOOKUP function.
=HLOOKUP("March", B1:G2, 2, FALSE)
Typing the HLOOKUP formula.
The HLOOKUP function successfully searched for the month “March” in the first row of the specified table_array (B1:G1) and returned the corresponding value from the second row (B2:G2), which is 210
.
The parameters we provided were:
"March"
is the lookup_value, the month we want to find the sales figures for.B1:G2
is the table_array, the range of cells that contains the data we want to search.2
is the row_index_num, indicating that we want to retrieve the value from the second row of the table_array (where the sales figures for "Laptops" are located).FALSE
specifies that we want an exact match for the lookup_valueThrough this example, we saw how HLOOKUP can be used to efficiently retrieve specific data from a horizontal dataset based on a given criterion.
What if we’re not looking for an exact match in the data?
Consider a scenario where a catering company offers different menu packages for events based on the number of guests.
The pricing for these packages is tiered with specific prices for different guest count brackets. They follow a pricing strategy where the per-person cost decreases with higher guest count brackets, encouraging bookings for more guests.
Their pricing card looks as shown below:
Catering company data.
You have been tasked to find a quick way to determine the total price for any given booking, provided the estimated number of guests, in line with the pricing strategy.
We’ll see how the approximate match parameter of HLOOKUP is handy in such a situation. Let’s follow the earlier steps again:
Selecting the cell to perform HLOOKUP.
=HLOOKUP(B4,B1:E2,2,TRUE)
Performing HLOOKUP operation.
After executing the HLOOKUP function in Step 3, cell B5 will display the value of $960. This is the per-person price for the guest count of 149 since it falls in the bracket of above 100 but less than 150.
Here’s the formula we entered:
The HLOOKUP function successfully searched for the nearest guest bracket less than or equal to 149 in the top row of the specified table_array (B1:E1) and returned the corresponding per-person package price from the second row (B2:E2), which is $960.
This example shows us how HLOOKUP with an approximate match is useful in efficiently retrieving data based on a tiered or ranged structure in a horizontal dataset.
This tutorial introduced you to the syntax of HLOOKUP in Microsoft Excel and helped you understand how to use them for your data using multiple examples.
#excel