HLOOKUP Formula in Excel (Table of Contents)
Excel HLOOKUP Formula
Hlookup Formula is used to lookup the value in a horizontal sequence by searching the value in the selected row and returning the value as per the row index number which we select. By this we can map and look up the value in horizontal.
Syntax of HLOOKUP Formula in Excel:
=HLOOKUP( Lookup value, table, row_index,[True Or False])
Explanation of Syntax:
- Value: The value that we want to lookup.
- Table: Data table or range in which you want to find the value.
- row_index: The row number that contains the value.
- True Or False: 0 or false for an exact match and 1 or True for an approximate match.
We generally use 0 or false to get the exact lookup value and not the nearest one. There are some situations wherein we use 1 or True match, like finding the temperature, sales percentage, etc.
How to Use the HLOOKUP formula in Excel?
We will now learn how to write this formula to get the lookup value. Let’s understand this formula with some examples.
Example #1
Below are the sales for 6 months. We want to find out the sales for April month from the below data table.
We will now write the formula in cell B5 as below:
Press Enter to see the result.
An explanation of the example is given step by step.
The formula is = HLOOKUP(B4,B1:G2,2,0)
Here, B4 is the lookup value. We want to lookup for April month sales.
B1:G2 is the table or data range from where the formula will lookup for the value.
2 is the row number to get a result from.
0 is the exact match for the lookup.
Example #2
Let’s say we have got subject-wise marks of some students in a data table.
Using this table, find out the marks in English for Cole.
We will write the formula for this as below:
Press Enter to get the result.
We find out that Cole got 32 marks in English.
Explanation of the formula:
=HLOOKUP(E1,B1:G4,3,0)
Here, E1( Cole) is the lookup value as we want to find the marks of Cole.
B1:G4 is the table from where the formula will lookup for the value.
3 is the row whose value the formula will return.
0 or false for an exact match.
Example #3
Taking the above example, we want to find the marks in Maths for students who have got 75 marks in science. We will write the formula as below:
Press Enter to view the result.
We got the result of 71. Ricky scored 75 in science and 71 in maths.
Explanation of the formula:
The formula used is = HLOOKUP(75,B2:G4,3,0)
75 here is the lookup value.
B2: G4 is the table for lookup.
3 is the row number.
0 is for an exact match.
Example #4
We have sample data of sales made by employees. The employee IDs of the sales executive are given in the data below.
Suppose we want to know the sales 4 for Emp id FIS1149 from the above table. For this, we will write the formula as shown.
Press Enter to see the result.
Explanation of the formula:
Formula used is =HLOOKUP(B8,B1:F6,5,0)
B8 is the lookup value.
B1: F6 is the cell range or table in which the formula will lookup for the value.
5 is the row number.
0 for an exact match of the lookup value.
And so we got the result 48.
Example #5
We will look into some examples where we use an approximate match that is True or 1. In the below table, we have the temperature of different cities. You have to find out which city has a certain temperature mentioned in cell B5.
We will write the formula in cell B5.
Press Enter to see the result.
Mumbai has an approximate 40-degree celsius tempearature.
Explanation of the formula:
Formula used is =HLOOKUP(B4,B1:F2,2,TRUE)
B4 is the lookup value.
B1: F2 is the range or table where the formula will lookup for value.
2 is the row number.
True for an approximate match.
An approximate match is used to get the approximate value or a value falling within certain limits, say a percentage from 25% to 30%.
Example #6
We can also use wildcards with the HLOOKUP function, depending upon the requirement. We use ‘?’ to match one character ‘*’ to match multiple characters in sequence. We have sample data containing sales by different employees.
Suppose we want to know an employee’s sales, but we cannot remember the exact name of that employee.
Like we want to know sales of Cathy, but we can only recall Cat.
In this case, we will use a wildcard in conjunction with the HLOOKUP formula.
Press Enter to see the sales for Cathy.
Explanation of the formula:
Formula used is =HLOOKUP(“Cat*”,B1:G2,2,0)
Here cat* is the lookup value. We put the wildcard ‘*’ after Cat so it can match the remaining characters of the complete name.
B1:G2 is the table from which the formula will lookup for the value.
2 is the row number.
0 is for an exact match.
This is how we can use the HLOOKUP formula in conjunction with wildcards.
From the above examples, we now have a good understanding of as to how and when to use the HLOOKUP formula in Excel.
Things to Remember
- We might get a #N/A error message at times while using this function. This happens because the range lookup is set to exact match, and the formula is unable to find the lookup value in the table/range.
- REF error arises when the row index number is less than the number of columns in the table array.
- This formula only returns one value. It can be the first value that matches the lookup value.
- This formula can be used with wildcards as well to match the lookup value.
- This formula is not case-sensitive.
- The formula will return #VALUE! error if the row index number is less than 1.
Recommended Articles
This has been a guide to HLOOKUP Formula in Excel. Here we discussed using the HLOOKUP Formula in Excel and practical examples and a downloadable excel template. You can also go through our other suggested articles –
