[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Syntax<\/h2>\n
[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=HLOOKUP(search_key, range, index, [is_sorted])[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
HLOOKUP(search_key, range, index, [is_sorted])<\/strong><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let\u2019s try out a few examples. Here is a test data set with sales figures for a group of salespeople. I will answer various business case questions using HLOOKUP. You\u2019ll see how the index parameter value affects the outcome.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/HLookup-image-1-1.png” alt=”hlookup-function-google-sheets-1″ title_text=”hlookup-function-google-sheets-1″ align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n I\u2019ll choose first example here to dissect and ensure that you understand the basic use of the function. The formula =Hlookup(11876,B1:K3,2,False)<\/strong> is used to tell the function to search for the value 11,876 within the range of cells from B1 to K3. Once it finds the value, it is instructed to return the data in the second row of the column it found the data in. The False indicates that the data is not sorted, and that you want an exact match to the search key.<\/p>\n Here\u2019s an interesting thing to note in the last case (row # 11 above). The function returned an #N\/A error. Why? That is, as explained in the error description, because the $15000 sales figure does not exist in the first row. Since I specified that I want an exact match (by indicating False for the is_sorted<\/strong> parameter), it cannot return an appropriate value.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the examples, I have chosen FALSE for the last parameter, which tells the function to find an exact match with the search key. This is typically how the HLOOKUP function is used. But what happens when you use True instead to find an approximate match?<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/HLookup-image-2-1.png” alt=”hlookup-function-google-sheets-2″ title_text=”hlookup-function-google-sheets-2″ align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Here, there are two examples where we use HLOOKUP to find the person with $12,000 of sales. The first function uses an is_sorted<\/strong> parameter of False, which tells the function to find an exact match. Because there is no salesperson with exactly $12,000 of sales, the function returns an error.<\/p>\n The second example is identical except it uses True instead, which tells the function that the data is sorted and it should find an approximate match. The function then tries to find the sales number that is closest to $12,000, and returns the name of the corresponding salesperson \u2013 Finch.<\/p>\n Notice that the salesperson closest to $12,000 sales is actually Gary. The HLOOKUP function finds an approximate match by finding the value that is closest but not more than the search key. It therefore returns Finch here, even though Gary is technically closer to the search key. This is an important feature to take note of, since it could impact your results when using this function!<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Here is a note from the official documentation<\/a> of the HLOOKUP function:<\/p>\n Using an incorrect sort type may cause incorrect values to be returned.<\/p>\n<\/blockquote>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now that you\u2019re getting more familiar with the function, let\u2019s try an example data set in which the first row has sorted string values, as shown below. There are two examples each for all the three cases available, but with one distinction of is_sorted<\/strong> parameter. Please observe how the HLOOKUP function is behaving.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/HLookup-image-3-1.png” alt=”hlookup-function-google-sheets-3″ title_text=”hlookup-function-google-sheets-3″ align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You can see that the TRUE or FALSE value for is_sorted<\/strong> parameter doesn\u2019t really affect the behaviour when it finds the search_key<\/strong> within the first row of the range. But an interesting development happens when it doesn\u2019t find an exact match for the search_key<\/strong>. In the case of FALSE (row # 12), it was looking for an exact match. But in the case of TRUE (row # 13), it is looking for an approximate match, and instead of Greg, it returns the information for Gary. This behaviour may or may not be desirable depending on your situation!<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The data for this case is essentially the same as that used above, except there are non-sorted strings in the first row.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/HLookup-image-4-1.png” alt=”hlookup-function-google-sheets-4″ title_text=”hlookup-function-google-sheets-4″ align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Unlike the previous example, the use of TRUE and FALSE values for the fourth parameter is generating different results. Because the data is not sorted, the function cannot give an accurate approximate match. When the data is not sorted, that the results are correct only when I used FALSE for is_sorted<\/strong>.<\/p>\n So, what is the bottom line?<\/p>\n Never lie to Google Sheets in the is_sorted parameter \u2013 if you do, it will return the favor!<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n There will be times when you may encounter multiple instances of the same value in the first row. In the example below, Barry occurs twice. What happens if you use the HLOOKUP function in such a scenario?<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/12\/HLookup-image-5-1.png” alt=”hlookup-function-google-sheets-5″ title_text=”hlookup-function-google-sheets-5″ align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You may have noticed, it picks the first Barry it encounters in the list, and doesn\u2019t consider the second Barry.<\/p>\n [\/et_pb_text][et_pb_text admin_label=”Connections B – Merge, split, filter – Vertical” module_class=”sheetgo-post-no-shadow-img md2-contained-button vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” text_font=”||||||||” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” saved_tabs=”all” global_colors_info=”{}” global_module=”50477″ theme_builder_area=”post_content”]<\/p>\n Merge, split, and filter spreadsheets<\/span> [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n There are two problems with this function:<\/p>\n There\u2019s an alternative that solves the above two problems. Please go through the explanation for the INDEX and MATCH function combination.<\/a> It is explained in the context of VLOOKUP function, but is equally applicable as a HLOOKUP alternative.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ global_module=”26540″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" The HLOOKUP function is perhaps not as famous and widely used as its counterpart, VLOOKUP function. However, the two functions are the same, with the HLOOKUP function simply being the transposed version of VLOOKUP. It stands for \u2018Horizontal Lookup\u2019. The function searches for a key value in the first row of the input range, and […]<\/p>\n","protected":false},"author":40,"featured_media":29519,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"The HLOOKUP<\/a> formula is perhaps not as famous and widely used as its counterpart, VLOOKUP<\/a> formula. But, this too serves similar purpose, albeit in a transposed form. It stands for 'Horizontal Lookup'. This searches for a key value in the first row of the input range. And, it returns the value of a specified cell from the column where it finds the key. We can expect an error if the key doesn't exist.\n\n
How to use HLOOKUP function<\/h2>\n
Numbers in the first row<\/h3>\n
The curious case of [is_sorted]<\/h3>\n
\n
Use case: Sorted strings in the first row<\/h3>\n
Use case: Unsorted strings in the first row<\/h3>\n
Use case: Multiple matches in the first row<\/h3>\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/connections-top-processors-main.webp\")
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\n
Regardless of the file format, Sheetgo can handle it.<\/span><\/p>\n![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/available-on-google-workspace-button-text.webp\")
<\/a><\/p>\nDrawbacks with the HLOOKUP function<\/h3>\n
\n
Syntax<\/h3>\nHLOOKUP(search_key, range, index, [is_sorted])<\/span><\/strong>\n
\n \t
Usage: HLOOKUP Formula<\/h3>\n
Use case: Numbers in the first row<\/h5>\nLet us try our hands on a few examples. Here is a test data with sales figures that a set of salespersons generated. We tried to answer various business case scenarios, using this formula. You'll observe the how index<\/strong><\/span> parameter value affects the outcome.\n\n
![\"HLOOKUP](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-63.png\")
\n\nHere's an interesting thing to note in the last case (row # 11 above). The formula returned an #N\/A error. Why? That is, as explained in the error description, because the $15000 sales figure does not exist in the first row. Therefore, it cannot return an appropriate value.\n<\/h5>\n
The curious case of [is_sorted]<\/span><\/h5>\nIn the examples, we have chosen FALSE for the last parameter if the first row within the input range is not sorted in ascending order. We chose TRUE otherwise. Now, what if we tried to do the opposite?\n\n
![\"HLOOKUP](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-64.png\")
\n\nThe second case worked out well. But the first one, not as much! Why is that? Here is a note from the official documentation<\/a> of HLOOKUP formula:\nUsing an incorrect sort type may cause incorrect values to be returned.<\/blockquote>\nSo, there, they said it themselves! It will not give us expected results when we go with the TRUE option when the first row is not sorted. However, if we have sorted values in the first row, using TRUE for the last parameter will result in a much better performance.\n
<\/h5>\n
Use case: Sorted strings in the first row<\/h5>\nNow that we got our hands greased with numbers, let us try with an example data set, in which the first row has sorted string values, as shown below. There are two examples each for all the three cases available, but with one distinction of is_sorted<\/strong><\/span> parameter. Please observe how the HLOOKUP formula is behaving.\n\n
![\"HLOOKUP](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-65.png\")
\n\nApparently, the TRUE or FALSE value for is_sorted<\/strong><\/span> parameter doesn't really affect the behavior when it finds the search_key<\/span><\/strong> within the first row of the range. But an interesting development happens when it doesn't find a match for the search_key<\/strong><\/span>. In the case of FALSE (row # 12), it was looking for an exact match. But in the case of TRUE (row # 13), it apparently is looking for a near match. This, obviously, may not be desirable in many cases.\n<\/h5>\n
Use case: Unsorted strings in the first row<\/h5>\nThe data we are considering for this use case is essentially the same as that used above. Except, we have non-sorted strings in the first row.\n\n
![\"HLOOKUP](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-66.png\")
\n\nUnlike the previous example, the use of TRUE and FALSE values for the fourth parameter is generating different results. And we can deduce that the results are correct only when we used FALSE for is_sorted<\/strong><\/span>.\n\nSo, what is the bottom line?\nNever lie to Google Sheets in the is_sorted<\/span><\/strong> parameter! Seemingly, it returns the favor!<\/blockquote>\n
<\/h5>\n
Use case: Multiple matches in the first row<\/h5>\nThere will be instances where we may encounter multiple instances of the same value in the first row. In the example below, Barry occurred twice. What happens if we use HLOOKUP formula in such scenario? Let's find out.\n\n
![\"HLOOKUP](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/10\/frame-generic-67.png\")
\n\nYou may have noticed, it picks up the first Barry it encounters in the list. And it doesn't consider the the second Barry within the row.\n<\/h5>\n
Use case: Drawbacks with the HLOOKUP formula<\/h5>\nThere are two problems with this formula:\n
\n \t