Excel | VLOOKUP vs HLOOKUP – What’s the Difference + Example

The VLOOKUP function is one of the most commonly used Excel functions in general. This function returns a value we are looking for from a particular column of a table containing many rows and columns. The function supports 2 modes – full and approximate match.

In addition to the VLOOKUP function, there is a similar HLOOKUP function. What is the difference between them?

  • VLOOKUP – the function gradually processes the rows of the first column of a certain table and returns the corresponding value from the column’s ordinal number (which we choose in the function) of the particular table
  • HLOOKUP – the function gradually processes the columns from the first row of a certain table and returns the value from the sequence number of the table row

It looks complicated, we can better understand the difference with examples (see below).

The Difference between VLOOKUP () and HLOOKUP () in Examples

Example 1 – VLOOKUP

Let’s have a table with the children’s names, their favorite toys, and information if they often get naughty. The table contains 7 children (in practice it can be a table with thousands of records). We have the following task – Using the formula to find some information based on the name of the following children – Kamila, Petr, Jakub

  • The names of the children are on separate rows
  • The most popular toy is in the 2nd column
  • Naughty? – is in column 3 of the table

vlookup vs hlookup example

In this case, we will use the VLOOKUP function. Based on Name, we are looking for a value for the 2nd (toy) and 3rd (naughty) column from the table containing all children.

vlookup example in excel - result

Values for a boy named James are not available because Jakub is not presented in the main list.

Note: In practice, we must select a value that is unique for the VLOOKUP function. This is not always the case with children’s names (2 different children may have the same name). But it’s OK for now. In this case, the names are unique.

Example 2 – HLOOKUP

Now let’s imagine that we have the same data, but the appearance of the default table is quite different and looks like this:

  • We now have the names of the children as columns
  • The most popular toy and Angry? we have in rows
  • The values are inside the table

Hlookup example in excel

In this situation, we can no longer use the VLOOKUP function, and we will use the HLOOKUP function instead.

hlookup function in excel example - result2

Conclusion – Let’s repeat the difference between VLOOKUP and HLOOKUP again

VLOOKUP – searches the ROWS of the first column of a table and if it finds a match (Name), it returns the value from the required column – in our case the 2nd column for the most popular toy and the 3rd column for Naughty?

HLOOKUP – searches the COLUMNS of the first row of a table and if it encounters a match (Name), returns the value from the required row – in our case the 2nd row for the most popular toy and the 3 row for naughty?

Rate this post
Category: Excel functions

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody, banking and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

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