Blog_Banner_Asset
    Homebreadcumb forward arrow iconBlogbreadcumb forward arrow iconData Sciencebreadcumb forward arrow iconWhat is HLOOKUP in Excel? Function, Syntax & Examples

What is HLOOKUP in Excel? Function, Syntax & Examples

Last updated:
24th Aug, 2022
Views
Read Time
6 Mins
share image icon
In this article
Chevron in toc
View All
What is HLOOKUP in Excel? Function, Syntax & Examples

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. 

ApplesOrangesPears
5510
6811
7912
FormulaDescriptionResult
=HLOOKUP(“Apples”, A1:C4, 2, TRUE)Locates up “Apples” in row 1 and gives you the value from row 2 that’s in the same column (column A).5
=HLOOKUP(“Oranges”, A1:C4, 3, FALSE)Locates  “Oranges” in row 1, and gives you the value from row 3 that’s in the same column (column B).8
=HLOOKUP(“B”, A1:C4, 3, TRUE)Locates “B” in row 1, and gives you the value from row 3 in the same column. This is because an accurate match for “B” is not found, and the largest value in row 1 less than “B” is used: “Apples,” in column A.6
=HLOOKUP(“Pears”, A1:C4, 4)Locates “Pears” in row 1, and gives you the value from row 4 in the same column (column C).12
=HLOOKUP(3, {1,2,3;”a”,”b”,”c”;”d”,”e”,”f”}, 2, TRUE)Locates the number 3 in the three-row array constant, and gives you the value from row 2 in the same column, which is in this case, third. You will find three rows of values in the array constant, where a semicolon separates each row (;). “c” is found in the second row and in the exact column as 3. Therefore “c” is returned.

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. 

Profile

Rohit Sharma

Blog Author
Rohit Sharma is the Program Director for the UpGrad-IIIT Bangalore, PG Diploma Data Analytics Program.

Frequently Asked Questions (FAQs)

1What is the difference between HLOOKUP AND VLOOKUP?

The difference between HLOOKUP and VLOOKUP is that HLOOKUP searches for the target values inside the selected columns, whereas VLOOKUP searches for the target values inside the selected rows.

2What is an alternative to HLOOKUP?

There are a few alternatives for HLOOKUP. XLOOKUP is a good alternative to HLOOKUP that allows you to search for values in both columns and rows.

3Can you use HLOOKUP AND VLOOKUP together?

Yes, you can use both HLOOKUP and VLOOKUP together. You can use the two functions together through the nested function method. We can use a nested formula to combine the two functions and retrieve relevant values from structures such as tables.

Explore Free Courses

Suggested Blogs

6 Phases of Data Analytics Lifecycle Every Data Analyst Should Know About
78384
What is a Data Analytics Lifecycle? Data is crucial in today’s digital world. As it gets created, consumed, tested, processed, and reused, data goes
Read More

by Rohit Sharma

19 Feb 2024

Sorting in Data Structure: Categories &#038; Types [With Examples]
135838
The arrangement of data in a preferred order is called sorting in the data structure. By sorting data, it is easier to search through it quickly and e
Read More

by Rohit Sharma

19 Feb 2024

Data Science Vs Data Analytics: Difference Between Data Science and Data Analytics
67025
Summary: In this article, you will learn, Difference between Data Science and Data Analytics Job roles Skills Career perspectives Which one is right
Read More

by Rohit Sharma

19 Feb 2024

13 Exciting Python Projects on Github You Should Try Today [2023]
44346
Python is one of the top choices in programming languages among professionals worldwide. Its straightforward syntax allows software developers and dat
Read More

by Hemant

19 Feb 2024

Top 15 Python AI &#038; Machine Learning Open Source Projects
35912
Machine learning and artificial intelligence are some of the most advanced topics to learn. So you must employ the best learning methods to make sure
Read More

by Pavan Vadapalli

19 Feb 2024

Top 21 Python Developer Skills You Must Need To Become a Successful Python Developer
78377
Its intuitive syntax, extensive libraries and versatile integration capabilities have fueled incredible growth across web development and scientific c
Read More

by Rohit Sharma

19 Feb 2024

Most Frequently Asked NumPy Interview Questions and Answers [For Freshers]
28628
If you are looking to have a glorious career in the technological sphere, you already know that a qualification in NumPy is one of the most sought-aft
Read More

by Rohit Sharma

19 Feb 2024

Top 30 Python Pattern Programs You Must Know About
33847
Summary Pattern in Python or “Python patterns” is an essential part of Python programming, especially when you are just starting out with using algor
Read More

by Rohit Sharma

19 Feb 2024

Top 12 Fascinating Python Applications in Real-World [2024]
154864
It is a well-established fact that Python is one of the most popular programming languages in both the coding and Data Science communities. But have y
Read More

by Rohit Sharma

19 Feb 2024

Schedule 1:1 free counsellingTalk to Career Expert
icon
footer sticky close icon