Hlookup Formula or horizontal lookup formula is opposite of the vlookup, wherein vlookup we use a cell reference and search the value in a column and output the value in HLOOKUP formula we use the cell reference to search the value in a row and output the value after the data matches from the respective row.
HLOOKUP Formula in Excel (Table of Contents)
Excel HLOOKUP Formula
The syntax for the HLOOKUP formula in excel is as follows:
- Lookup_value – We have to mention the value to be found in the first row of the selected table. This can be a value or a reference or text. This is a mandatory field to be considered.
- Table_array – This is the table where the data is used to look for. We can use a reference to a range or range name. This is also a mandatory field to be considered.
- Row_index_num–This is the row number in table_array from which the matching value should be returned. If we mention row_index_num as 1 then this will consider 1st row of the table array. Similarly, if we take 2 then it considers 2nd row from the table array. Suppose if we take negative value in place of row_index_numor any number which is greater than the number of rows on selected table_array then HLOOKUP will return the #REF error value. This is also a mandatory field to be considered.
- Range_lookup – This is an optional field. Here we get a logical value i.e TRUE or FALSE. TRUE represents for the appropriate match and FALSE represents for an exact match. If we won’t consider either TRUE or FALSE then HLOOKUP by default will consider for an appropriate match. Suppose we selected FALSE to find an exact match but we didn’t find any exact match then #N/A will be returned.
What is HLOOKUP Formula in Excel
HLOOKUP is called as “Horizontal Lookup” which is a function in excel used to search for information in a row (also called as table array) in order to return the information from a different row in the same column. Excel HLOOKUP Formula also supports exact match, appropriate match and wildcards such as (* and ?) in order to find matches.
How to Use HLOOKUP Formula in Excel (with Examples)
Below are some of the examples of HLOOKUP formula in Excel.
Let’s assume that we have downloaded data of different projects with their cost that are involved in a particular project as per below format.
From the above picture, we can see that we got 10 projects i.e. from Project A to project J details with land cost, construction cost, Statutory approval cost, Overhead cost, and Finance cost. Assuming this data is downloaded from company ERP so the format is as shown.
Now we got a task to pull out only land cost and construction cost from the data for Project A, Project C, project E, Project G, Project J as below:
Let’s see how can we do this using HLOOKUP formula in Excel.
First, we have to select the cell where the data is to be pulled out, In our case B9. Type the Hlookup formula in excel and select the required attributes.
Lookup_value – We have to select the cell that has the name as per the data. Here A9, as we are looking for costs of Project A and this will take to the project A column. We also can type the name of the project in lookup_value but it should be the same as in the table but the drawback is we should type project name for every project that we consider. This is time-consuming so most of the people prefer by listing the names in cells and selecting that particular cell.
Table_array – We have to select the table that contains data and make sure we include the cells that we are looking for and also the row index number to avoid #NA & #REF! errors as data cannot be looked in the table if these inclusions are not done. In our case the table is A1: K6 and we also should freeze (type $ sign or we can use keyboard shortcut F4) the table which avoids changing in table array if we drag the formula.
Row_index_num – Here we have to provide the row number from which we want the data. In our example, we are looking at Land which is in 2 rows. The count of rows should start from the point where the table array is selected.
Range_lookup – This is an optional field but by default, it will take TRUE or 1. In our example, we are looking for an exact match so we take 0 or FALSE. Note: TRUE can be represented as 1 and FALSE can be represented as 0.
Now, press enter and you can see the Land cost of Project A in cell B9 and then drag the same formula towards down so we can get land costs of all projects that are mentioned as follows:
Similarly, we can get the construction cost of all the mentioned projects by changing the row_index_num from 2 to 3 because we can observe that row number of construction cost is 3 when we count from the starting of the table_array.
And the result would be as follows:
Now, press enter and you can see the Const cost of Project A in cell C9 and then drag the same formula towards down so we can get Const costs of all projects that are mentioned as follows:
In this example, we had seen how to use HLOOKUP formula in excel by selecting the cell that already contains the lookup value i.e. project names in our example.
Assume that we have performance data of a company for the year FY-19 and company want to give a hike for all the employees based on their performance.
As per company policy, there is a pre-defined criterion for giving hike which varies based on the target achieved.
From the above data, we have 15 employees and targets that they had achieved in the year FY-19.
Let’s see how can we get hike % for the employees based on their targets achieved.
Lookup_value –We have to select the target achieved as our lookup_value as we are looking for a hike based on the target achieved so select B5 as we are evaluating Prayag’s hike first.
Table_array – Select table from A1: F2 as this table consists of targets and their associated hike. Freeze the table which avoids the errors when we drag the formula for other employees.
Row_index_num – We should take row_index_num as 2 because the hike is in the 2nd row of the selected table
Range_lookup –We should give more attention while selecting the range_lookup. In our example, we have target achieved which is not an exact number from the table so we should go with an appropriate match i.e. TRUE or 1. This will consider the appropriate close number from the table array and will give the hike that is associated with that particular target.
The result is shown below.
In this result of Prayag, the target he had achieved is 1,85,556 which is in between 1,50,000 and 2,00,000 but it is closer to 1,50,000 compared to 2,00,000. Because of this reason, the hike he will get is 8%.
Similarly, if we drag the same formula to downwards we will the hike that each employee got based on their performance as follows:
This is how an appropriate match will be useful while working with HLOOKUP formula in excel.
Things to Remember
Most often error that we can observe in HLOOKUP is #N/A error, #VALUE! error and #REF! error.
- #N/A error – Occurs when “range_lookup” is FALSE and HLOOKUP function is unable to find the exact match of lookup_value from the given range.
- #VALUE! – Occurs when “row_index_num” is a negative value like -1,-2,-3 and so on.
- #REF! –Occurs when “row_index_num” exceeds the number of columns in “table_array”. For example, if table_array is A1: F10 and we select lookup_value as H, then #REF! error occurs.
HLOOKUP is case sensitive and will consider both “Uppercase” and “Lowercase” as same.
Ex: HANEEF or Haneef will be considered the same.
Only if lookup value is text in lookup value argument then we can use wildcard characters such as * or ? with column number (A, B, C…..) to look up.
It is always advisable to check for the duplicates and remove or can group them while performing excel HLOOKUP formula because HLOOKUP function will return only one value which is the first value that matches witch lookup value.
This has been a guide to HLOOKUP Formula in Excel. Here we discuss how to use the HLOOKUP Formula in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –