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
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.
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
In this situation, we can no longer use the VLOOKUP function, and we will use the HLOOKUP function instead.
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?
