VLOOKUP and HLOOKUP – Practical Examples

Lookup Functions

VLOOKUP and HLOOKUP are among the most frequently used tools in Excel, because they allow users to transfer large quantities of data based on a given criterion.

---

Please feel free to download the spreadsheet files we will use in this article:

---

In the picture we have here, there is a worksheet, which contains two tables. The one on the right is very similar to the table on the left but includes only two of the teams. Its content is empty and needs to be filled.   A formula can be used to find the corresponding fields in the table on the left and populate them here.

VLOOKUP is suitable for such purposes. It has four function arguments:                    

1. Lookup value: The value to be found, or in other words, the value which serves as key and makes the connection with the data source. (In this case these are Lazio and Fiorentina in cells H4 and H5.)
2. Table array: The table within which we look for the value that needs to be found (the table on the left, or let’s call it the source table, that is the table from which we will extract data).
3. Column index number: The column from which we would like to extract a value when there is a match.
4. Range lookup: Tells the formula whether to look for the closest match or for an exact match. The two possible values for this argument are TRUE or FALSE: when we select TRUE we are saying that it is OK to find the closest match, while FALSE indicates that we want an exact match.

It is important that the value, which needs to be found (the “Lookup Value”) must be in the first column of the source table.

For example, you can look at the table with football teams we have above.

The goal is to extract the information related to them from the source table.

The first step is to type +VLOOKUP, select as a lookup value H4 (which is the name of the team Lazio), and select a table in which we will try to find the cells from B4 to F12 – the source table. Then, Excel will show that the lookup value “Lazio” is contained in the first column of the selected table, which is good as it is a necessary condition.  

The next step is to type “2” as the third argument of the formula, because the “Country” field must be populated. It is the second column within the source table. The last argument will be “FALSE” because an exact match is needed.

The formula now should look like this:

=+VLOOKUP(H4,B4:F12,2,FALSE)

This way the function can recognize that the value we were looking for was Lazio. The result we obtained is from the second column of the source table – “Italy.”

But if the formula is copied and pasted in H5, where we have Borussia, Excel will show an error message. The “Not Available” sign is saying that Excel was not able to find a value, which matches the one that we searched within the first column of the table.

There was a problem as the source table changed its location. We can check that by pressing F2 to see which arguments are considered within the formula and verify that they are the ones, which had to be selected. The references in the formula must be fixed with dollar signs:

=+VLOOKUP(H4,$B$4:$F$12,2,FALSE)

 After we do that, we can copy the formula to the right. Again, Excel displays the same error message. This time the problem is that the lookup value changed its position. This needs to be taken care of as well, by impeding the lookup value from changing its column position, putting a dollar sign in front of the column reference:

=+VLOOKUP($H4,$B$4:$F$12,2,FALSE)

Our output is “Italy”. It is the same as before because we also need to change the number of the column from which VLOOKUP makes an extraction.

Changing the number to 3 should solve this problem. The result we obtain is “No,” and it is correct as Lazio did not participate in the Champions League. Now, what we need to do is copy the formula for the rest of the table, remembering to change the column index number when we make extractions from new columns.

Let’s take a look at how the HLOOKUP function works. Shall we?

It is very similar to VLOOKUP.  The only thing that changes is the direction. The difference is that VLOOKUP works in terms of columns (horizontally), while HLOOKUP works in terms of rows (vertically).

The lookup value is in the first row of the source table, and we need to indicate the row of the table from which to extract the necessary data.

Here you can see a source table on the left and a table to be filled on the right.

The HLOOKUP function requires the same arguments as VLOOKUP:

1. Lookup value
2. Table array
3. Column index number
4. Range lookup

Typing HLOOKUP we should select Milan as the lookup value (it lies in H4) and fix the column reference “H” only to block the column movement of our lookup value but allow it to remain flexible vertically.

We need to select the source table as well. I’ll fix all of the references to facilitate our work later on. HLOOKUP needs us to select a third argument – the row from which it should extract a value. Given that we are looking for “Country” and it is in the second row of the source table – the number we will type is “2.”

The fourth argument I will choose is FALSE because we need an exact match. After we have completed the selection of the function’s arguments, we can close the brackets.

Here’s the complete formula:

=+HLOOKUP($H4,$B$3:$F$7,2,FALSE)

The result that we obtain is Italy.

We can apply it for the rest of the table. The only thing that needs to be modified is the respective number of the row from which we need to extract data.