In this article, you will learn how to use the HLOOKUP formula in Google Sheets. This function is beneficial when you need to extract necessary information out of a data set containing lots of irrelevant items. Its function is to sort for a specific value in a row and return a value in a different row in the same column.
Generic formula
Search_key: This is a value based on which this formula looks for a specific item.
Range: This is the entire area within which this function looks for a specific item. The uppermost row should contain a particular keyword you enter as search_key.
Index: This is the position of a row within the range selected from which the formula pulls out requested information. For example, if the specified range is C5:E9 and you want to see the data in Row 6, this index number should be 2, as Row 6 is the second row in the chosen field.
[is_sorted]: You need to choose “FALSE” for an exact match or “TRUE” for the nearest match. You can leave this section blank, but it is taken as “TRUE” in this case. So, you are recommended to enter “FALSE” to avoid pulling out incorrect data.
Learn how it works by looking at the example below. Assume you are a finance manager, you have monthly revenue data by client, Raw Data in the picture below, and you need to sort a revenue amount from Cucumber Company in April 2022.
Assume you made a mistake in “search_key” and you entered “Mar 2021”, which should be “April 2022”. At the bottom part of the upper screenshot, you can see two “#N/A” next to the company names of “Tomato” and “Lettuce”. This is because there is no period that exactly matches “Mar 2021” in the topmost row in the selected range, precisely, B:15 through H:15, {Company, Jan 2022, Feb 2022, Mar 2022, Apr 2022, May 2022, Jun 2022}. However, if you use “TRUE”, as you observe at the bottom part of the second screenshot below, values are returned by the formulas even if “Mar 2021” does not exist in the raw data.
The major mistakes and causes of errors when you use the HLOOKUP are as follows:
The VLOOKUP formula is often used to find an item on a vertically long table, whereas the HLOOKUP function is usually used to sort data on a horizontally long table. The significant difference is, that the VLOOKUP formula searched the leftmost column for “search_key” while the HLOOKUP function seeks “search_key” for the topmost column in each selected range.
Read this article if you are interested in how to use the VLOOKUP function in Google Sheets.
There are two patterns of formula - (i) the HLOOKUP function in the VLOOKUP formula and (ii) the VLOOK function in the HLOOKUP formula. To combine the VLOOKUP and HLOOKUP functions, you need to tweak the data table in which you want to find specific data. See example formulas in the screenshots below. The third example shows the combination of INDEX and MATCH functions, which is the alternative to the combination of VLOOKUP and HLOOKUP formulas.
(i) The HLOOKUP function in the VLOOKUP formula
In this example, the HLOOKUP is used to find a number, which is included in the VLOOKUP formula as “index”. As the HLOOKUP function can search data only downwards under the uppermost column, it is ideal that its target row (in this case, “Vlookup:Index”) is the second row, right beneath the top row in the selected field to minimize the format change of the data table. As the HLOOKUP returns 5, which is in the second row in the “Apr 2022” column within the selected area, the VLOOKUP formula looks for the 5th column in the row named “Lettuce”, which returns “666”, as you can see.
(ii) The VLOOKUP function in the HLOOKUP formula
In the second example, similar to the first formula, the VLOOKUP formula works as an index number finder for the HLOOKUP formula. As a preparation, you should insert an additional column for HLOOKUP’s index number next to the “Company” column to the right because the VLOOKUP formula can search data right from the leftmost column. The VLOOKUP function returns 6 in the second column in the “Lettuce” row as the index number in the VLOOKUP is 2. Then, the HLOOKUP finds “666”, located in the 6th row in the “Apr 2022” column within the selected field.
If you are looking for an alternative method of the HLOOKUP formula, we can suggest you use a combination of INDEX and MATCH functions. If you have never tried the functions, you should learn them because they are more flexible than the HLOOKUP function. Also, the XLOOKUP would be another option. Read this article for your understanding of the XLOOKUP function.
Here’s a quick explanation of INDEX and MATCH. Check this page to understand how to use these functions.
The combination of INDEX and MATCH functions
In this combination of the INDEX and MATCH functions, the MATCH functions work as pathfinders for the INDEX formula. The INDEX formula needs an input of the entire range where we are looking for a solution, so we will index the full table The top row and left column are counted as first ones in a selected array. In the example above, the first MATCH gives where the target row is, and the second MATCH brings where the target column is to the INDEXfunction. With these values, the INDEX function gets the specific coordinate of the target value.
The major benefits of this combination, compared to the combination of the VLOOKUP and HLOOKUP, are:
1. You can apply the formulas to the existing table without adjusting the data table (e.g., inserting a new column or row for indexes of the formulas).
2. You don‘t need to make any changes to the formula when you make adjustments to the table, such as inserting new columns and rows for new information or a set of additional data, as long as the adjustments are made in the selected array in the formulas.
If you don’t get the right answer to your question in this article or you have other questions on lookup formulas, you may find the answers in the following articles.
How to Use VLOOKUP in Google Sheets
XLOOKUP - Google Sheets: Explained
INDIRECT Function in Google Sheets: Explained
How to Use INDEX Function in Google Sheets
How to Use MATCH Function in Google Sheets
XMATCH in Google Sheets: Explained
OFFSET Function in Google Sheets: Explained
Are you learning this formula to process financial data, build a financial model, or conduct financial analysis? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. For instance, you can easily compare your Budget versus Actual without any manual work. Furthermore, you can access other templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. You can also customize these templates as you want without breaking the automated data inflow.
To learn more about LiveFlow, book a demo.
Learn how to do this step-by-step in the video below 👇