Skip to content

The Uncommon HLOOKUP Function

This article will teach you all about the not so talked about HLOOKUP function!

What the HLOOKUP Function Does

The HLOOKUP function allows you to find a value that is related to some other value in your spreadsheet. The two values are related if they share the same column.

Take a look at the table below.

ID Number is related to Bananas, which is also related to 0.50.

Presented with the data above, you can use the HLOOKUP function to find a Fruit or Price using the ID Number.

For example, if you want to know which Fruit has an ID Number of 2, you can use the HLOOKUP function. It will tell you that the Fruit with an ID Number of 2 is Oranges.

How the HLOOKUP Function Works

The HLOOKUP function requires three arguments, but it accepts up to four.

The arguments for the HLOOKUP function are:

  1. Lookup_value
  2. Table_array
  3. Row_index_num
  4. Range_lookup

Lookup_value – Required. This is the value you want to look up to find a related value. This can be a typed-in value or a reference to a cell containing the value.

Table_array – Required. This is the range of cells the HLOOKUP function will look through to find the Lookup_value and return the related value. The HLOOKUP function will only look for the Lookup_value in the first row of the table array.

Row_index_num – Required. This is a number that designates which row of the Table_array the function should look in to get the return (related) value. This can be a typed-in number or a reference to a cell containing a number.

Range_lookup – Optional. This is a logical value (TRUE/FALSE) that determines if the function should look for an exact match or an approximate match.

  • TRUE – This is the default value. The HLOOKUP function will search for the greatest value that is less than or equal to the Lookup_value. The Table_array must be sorted numerically or alphabetically in ascending order for this to work properly.
  • FALSE – The HLOOKUP function will look for an exact match of the Lookup_value.

HLOOKUP Example 1 – Exact Match

Take a look at the example below.

In the example above, the first argument references cell B4 which contains the lookup value.

The second argument references the range B1:F2 which represents the table array that the function will look through.

The HLOOKUP function will only search for the lookup value in the first row of this range. The third argument represents the row number that the HLOOKUP function should look in to get the return value.

The fourth and final argument indicates that the HLOOKUP function should look for an exact match instead of an approximate match.

The function does its calculations and returns the final value of Violet as the favorite color for Claire.

HLOOKUP Example 2 – Approximate Match

Take a look at the example below.

In this example, the fourth argument is set to TRUE. As a result, the HLOOKUP function looks for the value closest to 88 but also less than and equal to 88.

Also, note that the scores are sorted in ascending order. This is necessary for the HLOOKUP function to look for an approximate match.

The HLOOKUP function finds that 80 is the closest value that is also less than and equal to 88, and it returns the corresponding grade in row 2, which is B.

This Post Has One Comment

  1. This is so cool, it’s like a sideways version of VLOOKUP! I can see how this could be helpful. And as always, your explanations are amazing!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top