HLOOKUP Function

The Excel HLOOKUP function searches for a certain value in a row and returns a value from a different row in the same column.

Tip: If you are using Excel for Microsoft 365, try the XLOOKUP function, an improved version of Excel lookup functions that work in any direction and return exact matches by default without optional arguments.

How to use the HLOOKUP function in Excel?

Use HLOOKUP if your table array is transposed (variables headers listed in a row). The HLOOKUP function searches for a given lookup value in the first row of a table. When it finds a match, the function returns a value from the row shown in that column. The HLOOKUP function supports wildcards in finding partial matches.

First, take a look at the syntax and function arguments.

Syntax:

The HLOOKUP function uses three required and one optional argument.

=HLOOKUP(lookup_value, table_array, row_index, [range_lookup]

Arguments

  • lookup_value is the value that you want to look up;
  • table_array is the range from which you want to find and retrieve data
  • row_index is the row number containing the return value
  • range_lookup is FALSE (or 0) for an exact match. Use TRUE (or 1) for an approximate match. The default value is TRUE.

HLOOKUP Function Examples

In this section, we will demonstrate examples of HLOOKUP.

Example #1 – Exact match

With an HLOOKUP function, we search the product name in the data table and return the value from the second (Price) row.

Create a named range first! Select the table array. This range is where you are looking for the lookup value. In this case, select range G4:J5. Locate the name box and type ‘data‘.

From now, the range G4:J5 refers to ‘data’.

hlookup-examples

Type the formula below in D3:

=HLOOKUP(B3,data,2,0)

  • B3 is the lookup value (Apple) we are trying to match in the table array (data).
  • Table array is the second argument; in this case, use the named range ‘data’, to simplify the formula.
  • The row index is 2. This row contains the return value we are looking for
  • We find an exact match, so use the ‘0’ (or FALSE) logical value as the 4th, optional argument

Result:

HLOOKUP-function-exact-match-result-1

Example #2 – Approximate Match

Sometimes, we need to use an approximate match instead of an exact match. In the example, we want to create categories based on sales. Therefore, the lookup value is “A” in cell C3.

The ‘data‘ named range is the same as we mentioned in the first example: G4:J5

Now change the 4th argument to TRUE (or 1):

=HLOOKUP(C3,data,2,1)

or use the function with the required three arguments only:

=HLOOKUP(C3,data,2)

Here is the result if you are using an approximate match:

HLOOKUP-approximate-match-example

The lookup value is 308, and the range_lookup argument is TRUE. Excel evaluates the formula, and no exact match is found.

In this case, the HLOOKUP function gets the nearest value (300) less than the lookup value and returns with “A”.

Range lookup argument

It’s time to summarize what we have learned about the range_lookup optional argument:

  • The range_lookup helps you decide whether you are trying to match an exact lookup value (TRUE) or something similar (FALSE). The default value is TRUE, which allows a non-exact match.
  • Apply the FALSE value if you need to require an exact match.
  • If range_lookup is TRUE, and HLOOKUP does not find an exact match, the function will return the largest value that is less than the lookup value.
  • HLOOKUP returns an exact value if one exists, even if you use TRUE as the last argument.
  • If range_lookup is TRUE, you must sort the lookup values in the first row of the table in ascending order. Otherwise, HLOOKUP may return an unexpected value.
  • In the case of the FALSE argument, it is not necessary to sort the table.