HLOOKUP function in Excel, syntax and detailed usage

HLOOKUP is one of the basic functions in Excel, helping to search and reference data on spreadsheets. Join TipsMake to learn how to apply it effectively in work and study.

Table of Contents:
1. HLOOKUP function, syntax
2. Specific examples
4. How to combine HLOOKUP with the IF function.

1. Learn the HLOOKUP function

The HLOOKUP (Horizontal Lookup) function in Excel is a function that searches for data horizontally and returns results from another row in the data table. This function is very useful when the values to be searched are on a horizontal row, unlike the Vlookup function used for vertical columns.

Syntax: =HLOOKUP(Lookup_value, Table_array, Row_index_ num, Range_lookup)

In there:

+ Lookup_value: The value to look up, can be entered directly or referenced to a cell on the spreadsheet.

+ Table_array: The limit table to search.

+ Row_index_num: The order number of the row to get data in the table to search, counted from top to bottom.

+ Range_lookup: exact search or approximate search with limited table, if omitted then default is 1.

2. Specific example with HLOOKUP function

Example 1 : Fill in the information in the Student Academic Ranking column based on the information table below:

HLOOKUP function in Excel, syntax and detailed usage Picture 1

With the formula in cell E6: =HLOOKUP(D6,$B$16:$F$17,2,1)

HLOOKUP function in Excel, syntax and detailed usage Picture 2

Example 2: Calculating allowances by position

In this example, we need to fill in the allowance from table 2 into the allowance table 1 corresponding to each person's position.

HLOOKUP function in Excel, syntax and detailed usage Picture 3

- In cell D4, we use the formula: =HLOOKUP(C4,$B$11:$E$12,2,0)

HLOOKUP function in Excel, syntax and detailed usage Picture 4

3. HLOOKUP function combined with IF function

In Table 1 is the name of the employee's group and the sales achieved. Based on Table 2, the employee's results are given with each group having a certain target. If the employee exceeds the group's target, the number is passed , otherwise it is not passed .

HLOOKUP function in Excel, syntax and detailed usage Picture 5

- In cell E4, we use the formula: =IF(D4>HLOOKUP(C4,$B$14:$F$15,2,0),"Passed","Not Passed") .

Then you will get the results as shown in the image below.

HLOOKUP function in Excel, syntax and detailed usage Picture 6

Thus, the HLOOKUP function in Excel is a powerful tool that helps you quickly and accurately search for data horizontally. Understanding the syntax and usage of this function will help you apply it effectively in work and study, especially when you need to reference data from a spreadsheet.

Besides HLOOKUP, Excel has many other useful functions such as the SUMIF function , which allows you to calculate sums based on conditions, helping you work more efficiently and optimize data processing.

Isabella Humphrey

Update 18 February 2025

You should read it

May be interested

LOOKUP function searches in Excel
the lookup () function returns a value from a data range consisting of 1 column or 1 row, or from an array. the lookup () function is an improvement of the two functions vlookup () and hlookup () because it has the additional function to distinguish the search area as rows or columns.
How to use the INDEX function in excel?
along with vlookup and hlookup, an extremely interesting search function that you should not ignore is the index function. how to use it? is it difficult or not?
Excel date function - Usage and examples
the date counting function in excel is one of the most effective functions of this software. because excel usually works in the field of statistics and calculations, it is extremely necessary to record dates. to help you understand this function, let's come to the following article of tipsmake.
How to use the IFS function in Excel 2016
the ifs function in excel is a logical function introduced in excel 2016. this function is a function that replaces the nested if function and is much easier to use. the ifs function checks one or more conditions and returns a value that meets the first true condition.
How to use MID functions to get strings in Excel
mid function in excel is a function that takes the middle character string corresponding to the value that the user requires to perform.
How to use the LOOKUP function in excel?
the lookup function is more advanced than the hlookup and vlookup functions. the lookup function can be used when you need to look at a row or a column and find a value
OR function in Excel, how to use the OR function, and examples
the following article details the meaning and usage of the or - logic function in excel. description: the or function is a function that returns the logical value in excel.
Conditional counting function in Excel
conditional counting function in excel. are you looking for conditional counting function in excel to use counting data during data processing in excel spreadsheets? the countif function is a conditional counting function in excel, below is the description, syntax and usage
PMT function in Excel - Usage and examples
the pmt function is one of the built-in financial functions of the excel software used to calculate the payment for a loan based on regular payments and a constant interest rate. the pmt function is not only useful for businesses, but also very practical for users if you want to calculate a loan.
ROUND function in SQL Server
the article will give you detailed instructions on how to use the sql server round () function with the syntax and specific examples to make it easier to visualize and capture the function better.