How to Use VLOOKUP and HLOOKUP
Together in Google Sheets
Table of contents
- VLOOKUP functions
- HLOOKUP functions
- VLOOKUP and HLOOKUP functions together
The VLOOKUP and the HLOOKUP functions are each useful in finding information vertically or horizontally from a table. But when we want to retrieve information from a matrix where the lookup values are defined on both the row and the column, the VLOOKUP function and the HLOOKUP function can be used in combination. Before we learn about how we can use the two functions together, let us get familiar with the two functions separately.
The VLOOKUP function looks for a value down the first column of a range for a key and returns the value of a specified cell in the row found. So it is useful only when the data are listed in columns. Another limitation is that the lookup value has necessarily to be in the right-most column; and resultantly, it can only look to the right of the lookup value to extract information.
Where the argument search_key indicates the value to search for (eg. 123, “dog”, E14), range signifies the range to consider for the search, index is the column index of the value to be returned with the first column in range numbered 1, and is_sorted indicates whether the column to be searched is sorted. The last argument is set to FALSE by default, which means that an exact match is returned; if it is set to TRUE, the approximate match result is returned. The syntax is case insensitive.
Let us consider an example. Shown above are the points acquired by the “Big Six” clubs of the English Premier League in the past five seasons. To fetch the points Manchester United got in the 2017-18 season, we can use the VLOOKUP functions.
Here, the search_key is “Manchester United” or we can simply use the cell number that Manchester United is in, which is A7; the range is A3 through B8; the index is 2, since it is value in the second row that we want retrieved; and the is_sorted can be set to default, which is FALSE.
So the syntax is: VLOOKUP(A7,A3:B8,2,FALSE)
The returned result is 81, as shown below:
This function looks for value horizontally across the lookup table. In the HLOOKUP function, the value the user is searching in the table or range must be in the top-most row. It is used when data is listed in a row, and so it is less useful than the VLOOKUP function in practice. It searches across the first row for the matching key and returns the value of a specified cell in the column found. While the VLOOKUP function searches for the value in the column, the HLOOKUP searches function searches for the value in a row.
Let us consider another example using the table given in fig. 1. This time, let us find the points that Manchester United earned in the 2020-21 season.
The search_key is “2020-21” or E2, the range is A2:F8, index 6 since Manchester United is situated in the sixth column in the range, and is_sorted FALSE. The returned value is 74.
VLOOKUP and HLOOKUP functions combination
The above two functions are useful so long as the values that we want to fetch are in the same column or row. But there are occasions when we want to retrieve values from different columns and rows. This can be achieved by making the column reference of the VLOOKUP formula dynamic. One of the ways of doing this is by combining the VLOOKUP and the HLOOKUP functions.
To understand how, let us consider an example:
The goal here is to find the point that Manchester United got in the 2019-20 season. Here the vertical lookup value is “Manchester United” and the horizontal lookup value is “2019-20”.
We modify fig.1 by adding a new row numbering the columns as shown under.
First, let us look into the HLOOKUP function. In cell D13, we write the formula =HLOOKUP(B13,B2:F3,2,false) which will look for the season 2019-20 in the range B2:F3 with index 2, and returns the value, the column number, which in this case is 4 as shown in Fig. 5 below.
Then, we combine the HLOOKUP function with the VLOOKUP function to find the points that Manchester United got in the 2019-20 season. To do so, we write the formula =VLOOKUP(A13,A4:F9,HLOOKUP(B13,B2:F3,2,false),false) in the cell C13 which will then return the result, which is 66 as shown in Fig. 6.
We can then find out the points acquired by each of the six clubs in different seasons by simply entering the desired club and season in the cells A13 and B13, respectively. So, for example, replacing “Manchester United” by “Arsenal” in cell A13 returns the result 56, which is the points that Arsenal got in the 2019-20 season. Further, changing 2019-20 to 2021-22 changes the points to 69, which the points Arsenal got in the 2021-22 season.
The VLOOKUP and the HLOOKUP functions are simple and useful functions which can be used to retrieve values from a single row or column. And they can be combined to perform fairly complex functions which they individually cannot.