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