How to Vlookup and Hlookup Together in Google Sheets

How to Vlookup and Hlookup Together in Google Sheets
Reading Time: 4 minutes
Using Vlookup and Hlookup Together in Google Sheets
Vlookup and Hlookup together in Google Sheets

We will learn how to use Vlookup and Hlookup together in Google Sheets in a combined fashion to refine your searches for a large table.

Background

VLOOKUP and HLOOKUP are two very popular formulas used in Google sheets. They perform vertical and horizontal searches, respectively. Using Vlookup and Hlookup together in Google Sheets can be very powerful. You can combine them to perform 2D searches in a table. It is a very popular practice which a person should know. You can achieve the same using Index Matching as well but anyway, it is a good practice to know about. 

Matrix Preparation

First of all your data needs to have lookup values on both top and left sides. For example, in the following Figure 1, in the given matrix the lookup value on the top consists of the quarter values, and on the left, we have the names of the salesperson. So for example, “Ross” did a sale of 67 units in Q3.

Matrix preparation
Figure 1: Matrix Preparation

You’ll also need to have an additional row below your first row which will act as an identifier for columns. This is required for the Hlookup-Vlookup combination, so if for some reason you can’t add the additional row, you can consider using Index Matching.

Syntax

Using Vlookup and Hlookup together in Google Sheets is pretty straightforward. But, before getting started, I’ll recommend going through Hlookup and Vlookup so that we already know their functionalities respectively.

Using both of them together is a fairly simple concept, we use the value returned by Hlookup as a row number for VLOOKUP. The syntax for the same is given below.

=VLOOKUP(search_key, range, HLOOKUP(search_key, range, 2, FALSE), FALSE)

Example: Using VLOOKUP and HLOOKUP together in Google Sheets

We come back to the table that we saw earlier.

Sample table
Figure 2: Sample Table

Suppose we want to find out Ross’s sales in Q3. This table passes the prerequisites for matrix preparation that we talked about earlier. Let’s First make reference cells for us to fetch the arguments for.

Referencing search parameters
Figure 3: Referencing Search Parameters

The formula that we will write will go in cell B13 and it will fetch the values for search terms from B11 and B12.

Now the formula for the given scenario would be the following.

=VLOOKUP(B11, A1:E8, HLOOKUP(B12, A1:E8, 2, FALSE), FALSE)

Sample usage
Figure 4: Sample Usage of Vlookup and Hlookup together in Google Sheets

As you can see that the value displayed is consistent with the value in the table. Lets see how it was calculated.

HLOOKUP(B12, A1:E8, 2, FALSE) would perform the horizontal lookup and search between the first row values (i.e. Q3). After it successfully finds the value it returns the corresponding column number which, in this case, is 4.

Working of HLOOKUP in combination
Figure 5: Working of Hlookup in the combination

=VLOOKUP(B11, A1:E8, HLOOKUP(B12, A1:E8, 2, FALSE), FALSE) will be calculated next. Since the HLOOKUP section returned 4, this expression was reduced to =VLOOKUP(B11, A1:E8, 4 , FALSE). It will perform a vertical lookup for the value “Ross” and return the correspond 4th column, which in this case is 67.

Working of VLOOKUP in the combination
Figure 6: Working of VLOOKUP in the combination

Conclusion

Hence, using VLOOKUP and HLOOKUP together in Google Sheets we were able to perform searches in a matrix. If you don’t want to add the additional row to your table you can check out Index matching. This article discusses why Index Matching is better than using Hlookup or Vlookup.

See Also

Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

Vlookup in Google Sheets: We will learn about Vlookup in Google Sheets with several examples. We will also learn some advanced Vlookup features.

Google Sheets: Hlookup | Quick Guide: Introduction to Hlookup in Google Sheets