Contents
10th day of the marathon 30 Excel functions in 30 days we will devote to the study of the function HLOOKUP (GPR). This feature is very similar to VLOOKUP (VLOOKUP), only it works with elements of a horizontal list.
Unfortunate function HLOOKUP (GLOW) is not as popular as its sister, since in most cases the data in the tables is arranged vertically. Remember the last time you wanted to search for a string? What about returning the value from the same column, but located in one of the rows below?
Anyway, let’s give features HLOOKUP (GPR) a well-deserved moment of glory and take a closer look at the information about this feature, as well as examples of its use. Remember, if you have interesting ideas or examples, please share them in the comments.
Function 10: HLOOKUP
Function HLOOKUP (HLOOKUP) looks up the value in the first row of the table and returns another value from the same column in the table.
How can I use the HLOOKUP (HLOOKUP) function?
Since the function HLOOKUP (HLOOKUP) can find an exact or approximate value in a string, then it can:
- Find sales totals for the selected region.
- Find an indicator that is relevant for the selected date.
HLOOKUP Syntax
Function HLOOKUP (HLOOKUP) has the following syntax:
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр)
- lookup_value (lookup_value): The value to be found. Can be a value or a cell reference.
- table_array (table): lookup table. Can be a range reference or a named range containing 2 lines or more.
- row_index_num (line_number): A string containing the value to be returned by the function. Set by the row number within the table.
- range_lookup (range_lookup): Use FALSE or 0 to find an exact match; for an approximate search, TRUE (TRUE) or 1. In the latter case, the string in which the function is searching must be sorted in ascending order.
Traps HLOOKUP (GPR)
Like VLOOKUP (VLOOKUP), function HLOOKUP (HLOOKUP) can be slow, especially when searching for an exact match of a text string in an unsorted table. Whenever possible, use an approximate search in a table sorted by the first row in ascending order. You can first apply the function MATCH (MORE EXPOSED) or COUNTIF (COUNTIF) to make sure the value you’re looking for even exists in the first row.
Other features like INDEX (INDEX) and MATCH (MATCH) can also be used to retrieve values from a table and are more efficient. We’ll take a look at them later in our marathon and see just how powerful and flexible they can be.
Example 1: Find sales values for a selected region
Let me remind you again that the function HLOOKUP (HLOOKUP) only looks for the value in the top row of the table. In this example, we will find the sales totals for the selected region. It is important for us to get the correct value, so we use the following settings:
- The region name is entered in cell B7.
- The regional lookup table has two rows and spans the range C2:F3.
- The sales totals are in row 2 of our table.
- The last argument is set to FALSE to find an exact match when searching.
The formula in cell C7 is:
=HLOOKUP(B7,C2:F3,2,FALSE)
=ГПР(B7;C2:F3;2;ЛОЖЬ)
If the name of the region is not found in the first row of the table, the result of the function HLOOKUP (GPR) will #AT (#N/A).
Example 2: Find a measure for a selected date
Usually when using the function HLOOKUP (HLOOKUP) requires an exact match, but sometimes an approximate match is more appropriate. For example, if the indicators change at the beginning of each quarter, and the first days of these quarters are used as column headings (see the figure below). In this case, using the function HLOOKUP (HLOOKUP) and an approximate match, you will find an indicator that is relevant for a given date. In this example:
- The date is written in cell C5.
- The indicator lookup table has two rows and is located in the range C2:F3.
- The lookup table is sorted by date row in ascending order.
- The indicators are recorded in line 2 of our table.
- The function’s last argument is set to TRUE to look for an approximate match.
The formula in cell D5 is:
=HLOOKUP(C5,C2:F3,2,TRUE)
=ГПР(C5;C2:F3;2;ИСТИНА)
If the date is not found in the first row of the table, the function HLOOKUP (HLOOKUP) will find the nearest largest value that is less than the argument lookup_value (lookup_value). In this example, the desired value is March 15. It is not in the date line, so the formula will take the value 1 January and return 0,25.
