{"id":574,"date":"2019-04-17T14:51:25","date_gmt":"2019-04-17T11:51:25","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=574"},"modified":"2019-05-06T12:55:59","modified_gmt":"2019-05-06T09:55:59","slug":"how-to-use-the-hlookup-function","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-hlookup-function\/","title":{"rendered":"How to use the HLOOKUP Function"},"content":{"rendered":"\n<p> When you start learning about the lookup functionality of Excel, <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-hlookup-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"HLOOKUP  (opens in a new tab)\">HLOOKUP <\/a><\/strong>will probably be one of the first functions you will stumble upon. <\/p>\n\n\n\n<figure class=\"wp-block-embed-youtube wp-block-embed is-type-video is-provider-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<span class=\"embed-youtube\" style=\"text-align:center; display: block;\"><iframe class=\"youtube-player\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/aSSKtowQrik?version=3&#038;rel=0&#038;showsearch=0&#038;showinfo=1&#038;iv_load_policy=1&#038;fs=1&#038;hl=en-US&#038;autohide=2&#038;wmode=transparent\" allowfullscreen=\"true\" style=\"border:0;\" sandbox=\"allow-scripts allow-same-origin allow-popups allow-presentation allow-popups-to-escape-sandbox\"><\/iframe><\/span>\n<\/div><\/figure>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-large-font-size\"><strong>Practice File:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-file\"><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/hlookup.xlsx\">hlookup<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/hlookup.xlsx\" class=\"wp-block-file__button\" download>Download<\/a><\/div>\n\n\n\n<div style=\"height:100px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Transcript<\/strong><\/p>\n\n\n\n<p>We will continue with the HLOOKUP function. In cell area B5 to J7 we can see the gas consumption and the cost in dollars, for certain distances calculated in miles.<\/p>\n\n\n\n<p>For example, for a 75-mile\njourney we need 10 liters of gasoline which cost 13 dollars.<\/p>\n\n\n\n<p>Let\u2019s suppose we want\nto look up the consumption in liters for a 100-mile journey and show it in cell\nI11. In such a small pool of data it is relatively easy to find the proper\nvalue and manually type it in the proper cell.<\/p>\n\n\n\n<p>Imagine if your data\nspanned to thousands of rows and columns. Finding out the needed value then could\nbe impossible.<\/p>\n\n\n\n<p>This is where Excel\u2019s\nlookup functions provide us with the solution.<\/p>\n\n\n\n<p>So in cell I11 we will use the hlookup function to get the value we need.<\/p>\n\n\n\n<p>The hlookup function searches the data of the first row of the cell area B5:J7 for a value, in our case it is 100, and returns the value of the cell that can be found at the same column and at a given row of the cell area. In our case the column is the second one and so the value is 15. <\/p>\n\n\n\n<p>We select the cell and\nnavigate to the Formulas tab. We click at insert function and set the category\nto Lookup &amp; Reference. We locate the HLOOKUP function and click OK. We can\nof course just type the name of the function after the equals sign.<\/p>\n\n\n\n<p>At the arguments window, we set the value that we are looking for at the \u201cLookup_value\u201d. We select the cell G11, which refers to the distance that we are looking for, the 100 miles. <\/p>\n\n\n\n<p>At the field \u201ctable_array\u201d we need to define the cell area where Excel will perform the search; this is the cell area B5 to J7. At the argument \u201cRow_index_num\u201d we define the row number where hlookup will look for the result of the function, so we type the value 2. <\/p>\n\n\n\n<p>The last field\ncontains only two options: TRUE &amp; FALSE. We can type the number 1 or 0. If\nwe type the number 0, Excel will only look for an exact match. In case there is\nno such value, Excel will display an error message. If we type number 1 and\nthere is not an exact match, Excel will find the lower closest match in the\nfirst column. In our case it would be 75 <\/p>\n\n\n\n<p>We need to note that\nin order to use this function with the last attribute set to 1, the cell area\nneeds to be sorted in ascending order by the row in which we want to look for\nthe value. In our case by the row \u201cmiles\u201d. <\/p>\n\n\n\n<p>We type 0 and click at\nOK. As a result, the value 15 will be shown, which corresponds to 100 miles. <\/p>\n\n\n\n<p>If we change the value of the cell G11 to 110 miles, we can see that an error message appears because there aren\u2019t any values available for 110 miles. <\/p>\n\n\n\n<p>The letter H at\nHlookup stands for Horizontal. So, HLOOKUP looks for a value, in our case 100,\nat the top row of a cell area, and when it finds it, it returns the value that\nis found at a cell with the same horizontal position. Which is another cell at\nthe same column of our data area.<\/p>\n\n\n\n<p>In our example, we have asked for the second row, that\u2019s why we had the result 15. In case we had searched for the third row, we would have had the result 19.5 <\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>When you start learning about the lookup functionality of Excel, HLOOKUP will probably be one of the first functions you will stumble upon. <\/p>\n","protected":false},"author":2,"featured_media":578,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[54,49,32],"class_list":["post-574","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-hlookup","tag-lookup-functions","tag-reference"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/hlookup.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-9g","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/574","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/comments?post=574"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/574\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/578"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=574"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=574"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=574"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}