What is the HLOOKUP function?
The HLOOKUP function is a function in Excel that stands for “horizontal lookup.” It is used to look up a value in a horizontal direction and return the value in the specified column of the row where the value is found.
What is the purpose of the HLOOKUP function?
The purpose of the HLOOKUP function is to look up a specific value in a horizontal table range and return the value in the specified column of the row where the value is found. It is commonly used to find and retrieve specific numerical information in large data tables.
Detailed explanation of HLOOKUP parameters
The HLOOKUP function has four parameters, which are:
- Lookup_value: the value to look up.
- Table_array: the range of cells to look up the value in.
- Row_index_num: the number of rows between the row where the lookup value is found and the first row of the range.
- Range_lookup: [optional] whether to use approximate matching mode, either TRUE or FALSE.
Examples of using HLOOKUP
Example 1: Finding which quarter a product had the highest sales
Suppose there is a sales data table, where the first row represents quarters, the first column represents product names, and each cell contains the sales quantity of the corresponding product in that quarter. Now, you need to find out which quarter a particular product had the highest sales.
| Product Name | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| Product A | 10 | 12 | 15 | 8 |
| Product B | 5 | 9 | 20 | 13 |
| Product C | 18 | 15 | 12 | 9 |
Suppose the product you want to find is “Product B”. You can use the following formula: =HLOOKUP("Product B",A1:E4,2,FALSE), where A1:E4 is the range of cells, 2 is the number of rows between the row where “Product B” is found and the first row of the range, and FALSE indicates that an exact match is required.
The result of this formula is “20”, which indicates that “Product B” had the highest sales in the third quarter, with a sales quantity of 20.
Example 2: Finding the closest value to a given number
Suppose there is a table with a list of numbers, and you need to find the number in the table that is closest to a given number.
| Number | 5 | 8 | 11 | 13 | 15 |
|---|---|---|---|---|---|
| Value | 3 | 10 | 7 | 9 | 12 |
Suppose the number you want to find is “11.2”. You can use the following formula: =HLOOKUP(11.2,A1:E2,2,TRUE), where A1:E2 is the range of cells, 2 is the number of rows between the row where the number is found and the first row of the range, and TRUE indicates that an approximate match is required.
The result of this formula is “10”, which is the closest value to “11.2” in the table.
Example 3: Finding the age of a specific person
Suppose there is a personnel information table, where the first column is names, the second column is ages, and the third column is genders. Now, you need to find the age of a specific person.
| Name | Age | Gender |
|---|---|---|
| John | 25 | Male |
| Emily | 28 | Female |
| Tom | 30 | Male |
Suppose the person you want to find is “Emily”. You can use the following formula: =HLOOKUP("Emily",A1:C4,2,FALSE), where A1:C4 is the range of cells, 2 is the number of rows between the row where “Emily” is found and the first row of the range, and FALSE indicates that an exact match is required.
The result of this formula is “28”, which indicates that Emily is 28 years old.
Conclusion
The HLOOKUP function in Excel is a useful tool for looking up specific values in horizontal data tables. By understanding the parameters of the function and practicing with examples, you can become proficient in using it to quickly and efficiently retrieve information from large data sets.
Pingback: Excel Function Tutorial (5): How To Use The SUMIF Function – App Softea