What is HLOOKUP in Excel? Function, Syntax & Examples
Updated on Nov 14, 2024 | 6 min read | 899.5k views
Share:
For working professionals
For fresh graduates
More
Updated on Nov 14, 2024 | 6 min read | 899.5k views
Share:
Table of Contents
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.
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.
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:-
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.
Apples | Oranges | Pears |
5 | 5 | 10 |
6 | 8 | 11 |
7 | 9 | 12 |
Formula | Description | Result |
=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. |
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.
Get Free Consultation
By submitting, I accept the T&C and
Privacy Policy
Start Your Career in Data Science Today
Top Resources