HLOOKUP in Excel is short for ‘Horizontal Lookup’. This function helps in searching for a certain value at the top of a row of a table, also known as a ‘table array’, to return a value from another specified row from the same column. 

When comparison values are located in a row at the top of a table of data, and you need to look down a particular number of rows use the HLOOKUP function in Excel. VLOOKUP is a similar function when comparison values are located in a column located to the left of the specified data. Excel experts opine that the HLOOKUP formula is convenient and easy to use. 

HLOOKUP is especially useful when working with data such as long lists. When working with columns containing multiple values in Excel, HLOOKUP makes it easy to associate target cells. HLOOKUP also enables us to automate changes in other associated cells by using the HLOOKUP function when you modify the target cell. HLOOKUP can also find partial matches, thus, proving to be extremely useful when we are not certain about our lookup values.

Here we will shed light on HLOOKUP in Excel and the HLOOKUP formula. 

Learn online data science certifications from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

How HLOOKUP Works

The HLOOKUP function is primarily used to retrieve a value from data located in a horizontal table. As mentioned before, it is similar to the “V” in the VLOOKUP function which is short for “vertical”. 

To successfully use the HLOOKUP function, make sure that the lookup values are located in the first row of the table and are moving to the right horizontally. This function supports approximate and accurate matching and uses wildcards (* ?) to find partial matches.

Explore our Popular Data Science Courses

Syntax

The HLOOKUP function locates a value from the first row of a table. After locating a match, the value is retrieved from that column presenting the row given. The syntax for the HLOOKUP function is:-

The syntax of the HLOOKUP function in Excel has four elements. They are as follows:-

• Lookup_value:  This is a mandated requirement in the HLOOKUP syntax. It locates the value you need to find in the first row of the table. Lookup_value can be a value, a text string, or a reference. 
• Table_array: A table array is also a required element in the syntax of HLOOKUP. A table of information in which data is looked up. Always use a reference to a range name or a range.
• The values present in the first row of table_array can be numerical, alphabetical, or logical values.
• When the range_lookup is TRUE, the values located in the first row of table_array need to be placed in ascending order: -3, -2, -1, 0, 1, 2, A-Z; otherwise, HLOOKUP may not present the correct value. If range_lookup is FALSE, there is no need to sort the table_array.
• The lowercase and uppercase text are equal.
• Row_index_num: This is a crucial requirement as well. The matching value will be returned from this particular row number in table_array. A row_index_num of 1 returns a value in the first row of the table_array, and likewise, a row_index_num of 2 returns the value in the second row of the table_array, etc. If the row_index_num is more than the number of rows on table_array, HLOOKUP will return the #REF! error value and if the If row_index_num is lesser than 1, HLOOKUP will return the #VALUE! error value.
• Range_lookup: This is an optional element in the HLOOKUP syntax. It is a logical value that identifies if you want to find an accurate or an estimated match with HLOOKUP. If TRUE or omitted, you will get an estimated match, i.e., if HLOOKUP fails to find an accurate match, you will get the next largest value lesser than lookup_value. If FALSE, HLOOKUP will locate an exact match. If it fails to find a value, #N/A is the returned error value.

Example Of HLOOKUP in Excel

An HLOOKUP example for easy understanding:

To get a better experience of using the following example, it is advised to copy the example data in the following table and paste it into cell A1 in a fresh Excel worksheet. To see the results of the formulas, select them, press F2, and then click Enter. 

Important Things To Note About HLOOKUP

• HLOOKUP is a case-insensitive lookup. For example, it will consider “HAM” and “ham” the same.
• The ‘Lookup_value’ should be the first row of the ‘table_array’ while using HLOOKUP. To look somewhere else, another Excel formula needs to be used.
• Wildcard characters like ‘*’ or ‘?’ are supported by HLOOKUP in the ‘lookup_value’ argument if the ‘lookup_value’ is alphabetical. 
• If HLOOKUP fails to locate the lookup_value, and the range_lookup is TRUE, the largest value lesser than the lookup_value is used.
• If lookup_value is < than the smallest value of table_array’s first row, HLOOKUP will generate the #N/A error value.
• If range_lookup is FALSE, the wildcard characters can be used as well. 

Read our popular Data Science Articles

Conclusion

You can find the HLOOKUP function in every version of Microsoft Excel 2016, 2013, 2010, 2007, and lower. Having Microsoft Excel skills and proficiency in lookup functions can significantly boost your career. 

Making a career in data analytics is a lucrative opportunity in the current industry. Data analysts work with huge volumes of data for extracting crucial information. To hone your skills in data analytics, you must join upGrad’s Data Analytics Certificate Program. Powered by Fullstack Academy, you will get a Caltech data analytics certificate upon completing the course. You will be able to learn data visualization skills, Python, and SQL through the course, which will pave your way for making a great data analyst.