HLOOKUP Function in Excel and how to use it?

Untitled design

Author: Sarthak Bhalerao

Table of Contents

  1. What is the HLOOKUP function?
  2. How does the HLOOKUP function work?
  3. How to use the HLOOKUP function?

What is the HLOOKUP function?

The HLOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. HLOOKUP stands for Horizontal Lookup and can be used to retrieve information from a table by searching a row for the matching data and outputting from the corresponding column. HLOOKUP searches for the value in a row. It can be used as a worksheet function (WS) in Microsoft Excel. It is entered as a part of a formula in a cell of the worksheet.

How does the HLOOKUP function work?

  • Syntax:

 HLOOKUP( lookup_value, table_array, row_index_number, [range_lookup])

  • Parameters:
  1. lookup_value – The value to search for in the first row of the table
  2. table_array – Two or more rows of data in an ascending order
  3. row_index_number – The row number in table_array from which the matching value must be returned. The first row is 1
  4. range_lookup – Enter FALSE to find an exact match. Enter TRUE to find an approximate match. This parameter is optional.
  • Returns:

The HLOOKUP function returns any data type such as a string, number, date, etc.
If you enter FALSE for the range_lookup parameter and no exact match is found, then the HLOOKUP function will return #N/A.
If you specify TRUE for the range_lookup parameter and no exact match is found, then the next smaller value is returned.
If row_index_number is less than 1, the HLOOKUP function will return #VALUE!.
If row_index_number is greater than the number of columns in the table, the HLOOKUP function will return #REF!.

 

  • Applies to:

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

How to use the HLOOKUP function?

 

Let us consider the example below. The marks of four subjects for five students are as follows:

Now, if our objective is to fetch the marks of student D in Management, we can use HLOOKUP as follows:

The result would be 72

Here, HLOOKUP is searching for a particular value in the table and returning an exact or approximate value.

Leave a Reply