Combine VLOOKUP and HLOOKUP Functions in Google Sheets

We often need to find specific information from a dataset in Google Sheets. We use the VLOOKUP function for vertical search and HLOOKUP to search any data placed horizontally. However, there are times when we need to search for specific information using both vertical and horizontal data. In these instances, we need to combine the VLOOKUP and HLOOKUP functions in Google Sheets. In this article, we will show some methods and alternatives to do this task in Google Sheets.


A Sample of Practice Spreadsheet

You can download the practice spreadsheet from the download button below.


2 Easy Ways to Combine VLOOKUP and HLOOKUP Functions in Google Sheets

Let’s assume we have a dataset that contains the number of medals some countries achieved in a tournament in some particular years.

combine vlookup and hlookup google sheets

Now from this dataset, if you want to look for the data that a particular team achieved how many medals in a particular year then you will need to search for data from both the rows and columns. This is when you will need to combine the VLOOKUP and HLOOKUP functions in Google Sheets. Follow this article to learn how to do that.


1. Using Helper Row

The first method you can use is to add a helper row to the dataset. You can add a new row and use HLOOKUP to search for data from the rows. Follow the steps below to do it by yourself.

  • First, select cell B5 in the dataset. You need to insert a row above this row.

Using Helper Row to combine vlookup and hlookup google sheets

  • Then, right-click on the mouse to see the context menu. And, choose Insert 1 row above from there.

  • After that, you will see a new row added to the dataset. Now, number the cells in that row starting from 1.

  • Next, make a small data table for the Country, Year, and Medals like the following image.

  • Now, insert the below-mentioned formula in cell C14. And give a Country name and Year input in the blank cells. You will get the medal number in C14.
=VLOOKUP(C12,B4:G10,(HLOOKUP(C13,C4:G5,2,FALSE)),FALSE)

Formula Breakdown:

  • HLOOKUP(C13,C4:G5,2,FALSE): Here, cell C13 contains the search key, C4:G5 is the range to search, 2 is the number of the row in the specified range, FALSE is to return the exact match as the search result.
  • VLOOKUP(C12,B4:G10,(HLOOKUP(C13,C4:G5,2,FALSE)),FALSE): Here, cell C12 will contain the search key for VLOOKUP. B4:G10 is the search range. The output of the HLOOKUP will be the index and FALSE is to return the exact match as the search result.

Read More: Alternative to Use VLOOKUP Function in Google Sheets


2. Applying ArrayFormula

Another way to combine VLOOKUP and HLOOKUP in Google Sheets is to use the ARRAYFORMULA function with these functions. The ARRAYFORMULA function can return multiple results from an array or multiple rows and columns. This will eliminate the need to add a helper row in the first place. Follow the steps below to learn how to use these functions together with the ARRAYFORMULA function to look for data using both row and column information.

  • First, select the cell C12 and input the formula in the formula bar.
=ARRAYFORMULA(VLOOKUP(C11,B4:G9,HLOOKUP(C12,{B4:G4;SEQUENCE(1,COLUMNS(B4:G4))},2,0),0))
  • Next, it will show the number of medals in C13.

Applying ArrayFormula

Formula Breakdown:

  • COLUMNS(B4:G4): The COLUMNS function will return the number of the total columns of the specified range.
  • SEQUENCE(1,COLUMNS(B4:G4)): Here, the SEQUENCE function will make sequential numbers starting from 1. Therefore you won’t need to create a helper row as in the earlier method.
  • ARRAYFORMULA(VLOOKUP(C11,B4:G9,HLOOKUP(C12,{B4:G4;SEQUENCE(1,COLUMNS(B4:G4))},2,0),0)): This will return the result using the combination of VLOOKUP and HLOOKUP.

Read More: Match from Multiple Columns in Google Sheets (2 Ways)


Alternative to Combining VLOOKUP and HLOOKUP Functions in Google Sheets

There is an alternative to combining the VLOOKUP and HLOOKUP Functions in Google Sheets. You can use the INDEX and the MATCH functions to do the same thing. The INDEX function returns data from particular cells or a range of cells input. Additionally, the MATCH function returns the location of an item within a range that corresponds to a given value. Follow the steps to use the INDEX-MATCH functions to search data using rows and columns.

  • First, select cell C12 and insert the following formula.
=INDEX(B4:G9,MATCH(C11,B4:B9,0),MATCH(C12,B4:G4,0))
  • After that, it will show the search results according to Country and Year name.

Alternative to Combining VLOOKUP and HLOOKUP Functions in Google Sheets

Formula Breakdown:

  • MATCH(C11,B4:B9,0): It returns the relative position of a vertical value from the B4:B9.
  • MATCH(C12,B4:G4,0): It returns the relative position of a horizontal value from the B4:G4.
  • INDEX(B4:G9,MATCH(C11,B4:B9,0),MATCH(C12,B4:G4,0)): It gives the value of a cell intersecting the row and column numbers in the data table.

Read More: Find All Cells With Value in Google Sheets (An Easy Guide)


Things to Remember

  • You can use FALSE or 0 in the formula for an exact match and TRUE or 1 for an approximate match.
  • The formula may return errors in case of no matches.

Conclusion

We have tried to show you the method to combine VLOOKUP and HLOOKUP functions in Google Sheets. Hopefully, the examples above will be enough for you to understand the applications of the function. Please use the comment section below for further queries or suggestions. You may also visit our OfficeWheel blog to explore more about Google Sheets.


Related Articles

Maruf

Maruf

Hello everyone! This is Maruf Hasan. I am working as a Content Developer at Officewheel. Here we make content on google sheets. We share simple methods to make your google sheets journey enjoyable. I love solving problems, researching, and writing.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo