Excel Function Tutorial (3): How To Use The HLOOKUP Function

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 NameQ1Q2Q3Q4
Product A1012158
Product B592013
Product C1815129

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.

Number58111315
Value3107912

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.

NameAgeGender
John25Male
Emily28Female
Tom30Male

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.

1 thought on “Excel Function Tutorial (3): How To Use The HLOOKUP Function”

  1. Pingback: Excel Function Tutorial (5): How To Use The SUMIF Function – App Softea

Leave a Reply