Hot to use HLOOKUP Capa

Share with the world

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp
Share on reddit

Make a donation

You may also like...

How to use HLOOKUP function

HLOOKUP is not as well-known as its sister function, VLOOKUP, but it can surely be very useful and sometimes the only option available to solve an Excel problem.

If you are not yet familiar with VLOOKUP function, I strongly recommend that you read this article here, where we teach you everything you need to know about VLOOKUP.

HLOOKUP and VLOOKUP work basically the same way, the only difference is that the first one will look for data in a horizontal way, and then return the value in a certain row in the same columns of the value it was told to look for. And the second one will look for data in a vertical way, and then return the value in a certain column in the same row of the value it was told to look for. HLOOKUP searches in a horizontal way, while VLOOKUP does it in a vertical way, now you know what the H and V in their names stand for.

For a Portuguese version of this post, click here.

What is VLOOKUP for?

HLOOKUP lets you find data in any row in a given range based on other data in the topmost row of the selected range.

All you need to do is tell HLOOKUP a data to look for in the first row of a selected range, and it will return any value in a lower row on the same column of the data you told it to look for.

HLOOKUP function arguments

=VLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

In which:

lookup_value = The value or data you want to find the corresponding date in a lower row.

table_array = The range where lookup_value and the data you are looking for are.

row_index_num  = The row number where the data you want to get is, relative to the first row selected in table_array.

[range_lookup] = Optional argument that tells HLOOKUP how it should look for the data provided in lookup_value, if it should be an exact match or an approximated one.

Too lazy to read? Watch the video

How to use HLOOKUP function for an exact match

Here is a data table with the number of people who visited a park each month of the year.

Let’s say we want to find out how many children visited the par in June.

To do that we will use our HLOOKUP function in the following way:

lookup_value = JUN – Since we are using the months’ abbreviation, that’s what we should look for.

table_array = All the cells where our table is.

row_index_num = 4 – Meaning the children’s data in the 4th row of our data table. Or, the children’s data is in the 4th row starting counting from the row where June is.

[range_lookup] = FALSE – Because we want an exact match for June, and not something approximated, if else we would use TRUE.

Now let’s take a look at how that goes in our spreadsheet.

Our function was inserted in cell C10 of our spreadsheet, and here is how each argument goes.

lookup_value = C9 – Because JUN (June) is in cell C9, the function will read what is in that cell, and use it for the search. Alternatively, we could have typed June inside the function, without making a reference to C9, to do that we would just type “JUN” (With double quotes, they would me mandatory to tell Excel that it’s a text string), but using a reference to a cell makes it easier to see what the function is doing, and easier to make changes, as soon as we change the month in cell C9, HLOOKUP will automatically look for the new input.

table_array = C2:N7 – It’s the cell range where our table is and where the data should be looked up in.

row_index_num = 4 – Meaning the children’s data the 4th row of our data table. Or, the children’s data is in the 4th row starting counting from the row where June is.

[range_lookup] = FALSE – Because we want an exact match for June, and not something approximated, if else we would use TRUE.

Now the results.

As we can see, our HLOOKUP function worked just how we expected.

How to use VLOOKUP function for an approximated match

This is not as common as the use with an exact match, but in some cases, it’s necessary to tell our function that what we are looking for is not exactly what we told, but a close guess.

Notice that, for this to work, data in the first row of table_array MUST be sorted in ascending order.

In the table below, we have the names and ages of a few people that want to enter a park, but they have to pay for their tickets according to how old they are. We also have the ticket price table that tells the price for each age range.

Hot to use HLOOKUP 4
Hot to use HLOOKUP 4

What we need to do is put on cells D8:D12 what price each of them should pay to enter the park. To do that, in cell D8 we will use our HLOOKUP function like follows, and then just copy it down to all the cells we need in column D.

lookup_value = C8 – That’s where the first participant’s age is, and that’s what we want to look up in the price table.

table_array = C3:E5 – That’s where the price table with the prices that should be paid is.

row_index_num  = 3 – Meaning the price to be paid is in the 3rd row of table_array.

[range_lookup] = TRUE – Because we want an approximated match for the visitor’s age.

So, our function on cell D8 will be:

=VLOOKUP(C3,$F$4:$H$7,3,TRUE)

And then we just copy it down until cell D16. You might have noticed that I used $ inside the function, that is to lock, or freeze, the cell reference so it doesn’t change when I copy down the function. You can learn more about that clicking here.

Now the results.

Hot to use HLOOKUP 5
Hot to use HLOOKUP 5

Notice that although we don’t have all the possible ages in our price table, the function works normally. What it’s doing is looking at the guest’s age and checking one by one with the ages in the first row of the price table. So, for the last guest, Roger, who is 39 years old, it goes like this:

Is 39 greater or equal to 0 (first value in the price table)? Yes, it is! Now check the next one.

Is 39 greater or equal to 16? Yes, it is, check the next one.

Is 39 greater or equal to 60? No, it’s not, so return whatever is in the previous one.

And so, it returns $10.00 because that is the value in the same column of number 16 and in the 3rd row of table_array.

And also, notice that row number 4 has absolutely no influence in our function, it’s just something visual, since the only values that matter are the ones in the first row, and the ones in the row we want to get the data from. The same goes for when we are using an exact match.

HLOOKUP is not perfect

Although it’s a great function, it’s not perfect and has some limitations, here are some of them.

  • It’s not possible to look for values upwards, it always starts from the first row selected in table_array and downwards.
  • In case there are two cells that match the value you’re looking for, HLOOKUP will always only return the first one it finds.
  • When you use it on its own you have to tell it exactly the number for the row_index_num argument, which sometimes can be frustrating, especially if you need to change that value when you copy the function to other cells.

We can combine HLOOKUP with MATCH to solve the third problem mentioned, or we can use INDEX and MATCH together instead of HLOOKUP to solve the first and third problems mentioned.

Function Excel is 100% free, consider donating and sharing

Share with the world

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp
Share on reddit

Make a donation

2 thoughts on “How to use HLOOKUP function”

Comments are closed.

Check these other posts we have