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 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 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.
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 a student who has got 75 marks in science. We will write the formula as below:
Press Enter to view the result.
We got the result as 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 lookup value.
B2: G4 is the table for lookup.
3 is the row number.
0 is for an exact match.
Example #4
We have a sample data of sales made by employees. The employee id’s of 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 example where we use an approximate match that is True or 1. In the below table, we have a temperature of different cities. You have to find the 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 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 percentage from 25% to 30%.
Example #6
We can also use wildcards with HLOOKUP function, depending upon the requirement. We use ‘?’ to match a one character ‘*’ to match multiple characters in sequence. We have sample data containing sales by different employees.
Suppose we want to know the sales of an employee 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 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 #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 how to use HLOOKUP Formula in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
