{"id":6019,"date":"2024-10-21T18:01:00","date_gmt":"2024-10-21T12:31:00","guid":{"rendered":"https:\/\/www.complianceprime.com\/blog\/?p=6019"},"modified":"2024-10-17T15:07:14","modified_gmt":"2024-10-17T09:37:14","slug":"how-to-use-hlookup-formula-in-excel","status":"publish","type":"post","link":"https:\/\/www.complianceprime.com\/blog\/2024\/10\/21\/how-to-use-hlookup-formula-in-excel\/","title":{"rendered":"How to use HLOOKUP formula in Excel?"},"content":{"rendered":"\n<p>The <strong>HLOOKUP<\/strong> (Horizontal Lookup) formula in <a href=\"https:\/\/www.complianceprime.com\/subject\/10\/microsoft-excel\">Excel<\/a> is used to search for a value in the top row of a table or range and return a value in the same column from a specified row. It is particularly useful when you need to look up data that is organized horizontally (across columns).<\/p>\n\n\n\n<p>Other similar powerful formulas in Excel which you might have worked with are <a href=\"https:\/\/www.complianceprime.com\/blog\/2024\/05\/20\/how-do-you-use-vlookup-in-excel\/\">VLOOKUP<\/a> and <a href=\"https:\/\/www.complianceprime.com\/blog\/2024\/05\/24\/how-to-use-the-xlookup-function-in-excel\/\">XLOOKUP<\/a> which simplify the process of searching and retrieving data.&nbsp;<\/p>\n\n\n\n<p>In this blog we will explore the HLOOKUP formula.&nbsp;<\/p>\n\n\n\n<div style=\"color:#0E1851;margin-top:20px;font-size:28px;font-weight:bold;\">Related Webinars<\/div><div style=\"width:100%;height:auto;overflow:hidden;overflow-x:auto;margin:20px 0;\"><div style=\"width:calc(3 * 260px);\"><div style=\"width:250px;height:350px;background-color:#D2E0FF;background:url(https:\/\/www.complianceprime.com\/assets\/images\/wdt-back.png);background-repeat:no-repeat;background-size:cover;border-radius:10px;margin-right:10px;float:left;text-align:center;padding:25px 10px 0 10px;cursor:pointer;\" onclick=\"location.href='https:\/\/www.complianceprime.com\/details\/137\/excel-formulas-functions?utm_source=cp_blog'\"><img decoding=\"async\" style=\"width:135px;height:135px;border-radius:50%;border:2px solid #2B58B5;padding:3px;\" src=\"https:\/\/www.complianceprime.com\/image.php?src=https:\/\/www.complianceprime.com\/uploads\/img_upload\/066d531fed667563909fbd5701eb1c5e.jpg&w=200&h=200&zc=1&s=1\" alt=\"Speaker\"><div style=\"color:#0E1851;margin-top:5px;font-size:18px;font-weight:bold;line-height:22px;max-height:65px;overflow:hidden;\">Advanced Excel Functions: Lookup and Logical Tools<\/div><div style=\"clear:both;\"><\/div><div style=\"height:45px;\"><\/div><div style=\"font-size:12px;color:#2B58B5;margin-top:-10px;\"><strong>Speaker: <\/strong>Neil Malek<\/div><div style=\"width:120px;text-transform:uppercase;font-size:12px;color:#FB0351;border:2px solid #FB0351;border-radius:30px;padding:1px 5px;margin:10px auto;\">Learn More<\/div><\/div><div style=\"width:250px;height:350px;background-color:#D2E0FF;background:url(https:\/\/www.complianceprime.com\/assets\/images\/wdt-back.png);background-repeat:no-repeat;background-size:cover;border-radius:10px;margin-right:10px;float:left;text-align:center;padding:25px 10px 0 10px;cursor:pointer;\" onclick=\"location.href='https:\/\/www.complianceprime.com\/details\/120\/excel-dashboard?utm_source=cp_blog'\"><img decoding=\"async\" style=\"width:135px;height:135px;border-radius:50%;border:2px solid #2B58B5;padding:3px;\" src=\"https:\/\/www.complianceprime.com\/image.php?src=https:\/\/www.complianceprime.com\/uploads\/img_upload\/1310fe063de4c0d9a43a9d19bcfe9739.jpg&w=200&h=200&zc=1&s=1\" alt=\"Speaker\"><div style=\"color:#0E1851;margin-top:5px;font-size:18px;font-weight:bold;line-height:22px;max-height:65px;overflow:hidden;\">Microsoft Excel: Creating an Interactive Dashboard<\/div><div style=\"clear:both;\"><\/div><div style=\"height:45px;\"><\/div><div style=\"font-size:12px;color:#2B58B5;margin-top:-10px;\"><strong>Speaker: <\/strong>Mike Thomas<\/div><div style=\"width:120px;text-transform:uppercase;font-size:12px;color:#FB0351;border:2px solid #FB0351;border-radius:30px;padding:1px 5px;margin:10px auto;\">Learn More<\/div><\/div><div style=\"width:250px;height:350px;background-color:#D2E0FF;background:url(https:\/\/www.complianceprime.com\/assets\/images\/wdt-back.png);background-repeat:no-repeat;background-size:cover;border-radius:10px;margin-right:10px;float:left;text-align:center;padding:25px 10px 0 10px;cursor:pointer;\" onclick=\"location.href='https:\/\/www.complianceprime.com\/details\/168\/excel-tips-shortcuts?utm_source=cp_blog'\"><img decoding=\"async\" style=\"width:135px;height:135px;border-radius:50%;border:2px solid #2B58B5;padding:3px;\" src=\"https:\/\/www.complianceprime.com\/image.php?src=https:\/\/www.complianceprime.com\/uploads\/img_upload\/1310fe063de4c0d9a43a9d19bcfe9739.jpg&w=200&h=200&zc=1&s=1\" alt=\"Speaker\"><div style=\"color:#0E1851;margin-top:5px;font-size:18px;font-weight:bold;line-height:22px;max-height:65px;overflow:hidden;\">Time-Saving Excel Tips, Tricks and Shortcuts<\/div><div style=\"clear:both;\"><\/div><div style=\"height:45px;\"><\/div><div style=\"font-size:12px;color:#2B58B5;margin-top:-10px;\"><strong>Speaker: <\/strong>Mike Thomas<\/div><div style=\"width:120px;text-transform:uppercase;font-size:12px;color:#FB0351;border:2px solid #FB0351;border-radius:30px;padding:1px 5px;margin:10px auto;\">Learn More<\/div><\/div><\/div><\/div>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Syntax of HLOOKUP:<\/strong><\/h3>\n\n\n\n<p>=HLOOKUP(lookup_value, table_array, row_index_num,[range_lookup])<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>lookup_value<\/strong>: The value you want to search for in the top row of the table.<\/li>\n\n\n\n<li><strong>table_array<\/strong>: The range of cells that contains the data (including the row with the lookup value).<\/li>\n\n\n\n<li><strong>row_index_num<\/strong>: The row number in the table from which you want to retrieve the value. The top row is 1, the second row is 2, and so on.<\/li>\n\n\n\n<li><strong>range_lookup<\/strong> (optional): A logical value that indicates whether you want an exact match (FALSE) or an approximate match (TRUE).<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Step-by-Step Guide to Using HLOOKUP:<\/strong><\/h3>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Example:<\/strong><\/h4>\n\n\n\n<p>Suppose you have the following table, and you want to look up the sales value for &#8220;Product C&#8221; in the third quarter:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><\/td><td><strong>Q1<\/strong><\/td><td><strong>Q2<\/strong><\/td><td><strong>Q3<\/strong><\/td><td><strong>Q4<\/strong><\/td><\/tr><tr><td>A<\/td><td>500<\/td><td>700<\/td><td>900<\/td><td>850<\/td><\/tr><tr><td>B<\/td><td>600<\/td><td>650<\/td><td>850<\/td><td>950<\/td><\/tr><tr><td>C<\/td><td>700<\/td><td>800<\/td><td>950<\/td><td>900<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>You want to retrieve the sales figure for <strong>Product C<\/strong> in <strong>Q3<\/strong>.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Steps:<\/strong><\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Select the cell<\/strong> where you want the result.<\/li>\n\n\n\n<li><strong>Type the formula<\/strong>:<br><br>=HLOOKUP(&#8220;Q3&#8221;, A1:E4, 3, FALSE)<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>&#8220;Q3&#8221;<\/strong>: This is the value you&#8217;re looking for in the top row.<\/li>\n\n\n\n<li><strong>A1<\/strong><strong><br><\/strong>: This is the table range that includes the headers and data.<\/li>\n\n\n\n<li><strong>3<\/strong>: This is the row index for Product C (since Product C is in the 3rd row of the table).<\/li>\n\n\n\n<li><strong>FALSE<\/strong>: This indicates that you want an exact match for &#8220;Q3&#8221; in the top row.<\/li>\n<\/ul>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li><strong>Press Enter<\/strong>. The result will be <strong>950<\/strong>, which is the sales value for Product C in Q3.<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Detailed Explanation of Parameters:<\/strong><\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>lookup_value<\/strong>: Can be a specific value or a reference to a cell containing the value. For instance, instead of using &#8220;Q3&#8221;, you could reference a cell like B1 if B1 contains &#8220;Q3&#8221;.<\/li>\n\n\n\n<li><strong>table_array<\/strong>: The range must include the top row where you are looking for the <strong>lookup_value<\/strong> and the rows from which you want to retrieve data. Make sure the top row contains unique values.<\/li>\n\n\n\n<li><strong>row_index_num<\/strong>: This is the row number in the <strong>table_array<\/strong> that contains the value you want to return. For example, if you want to return data from the second row under the header, you would use 2.<\/li>\n\n\n\n<li><strong>range_lookup<\/strong>:\n<ul class=\"wp-block-list\">\n<li><strong>TRUE<\/strong> (default): Will find an approximate match. Useful when the data is sorted.<\/li>\n\n\n\n<li><strong>FALSE<\/strong>: Will find an exact match. It\u2019s often safer to use this option to avoid incorrect results.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Example with Approximate Match:<\/strong><\/h3>\n\n\n\n<p>Suppose you have the following data and want to find the approximate grade for a score of 85:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><\/td><td><strong>60<\/strong><\/td><td><strong>70<\/strong><\/td><td><strong>80<\/strong><\/td><td><strong>90<\/strong><\/td><td><strong>100<\/strong><\/td><\/tr><tr><td>Grade<\/td><td>D<\/td><td>C<\/td><td>B<\/td><td>A<\/td><td>A+<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>The formula:<\/p>\n\n\n\n<p>=HLOOKUP(85, B1:F2, 2, TRUE)<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Looks for 85 in the first row.<\/li>\n\n\n\n<li>As there is no exact match, it will find the closest smaller value (which is 80).<\/li>\n\n\n\n<li>The result will be <strong>B<\/strong>, the grade corresponding to 80.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Common Errors in HLOOKUP:<\/strong><\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>#N\/A<\/strong>: If the <strong>lookup_value<\/strong> is not found, or if you&#8217;re looking for an exact match and one doesn&#8217;t exist, you will get this error.<\/li>\n\n\n\n<li><strong>#VALUE!<\/strong>: Occurs if the <strong>row_index_num<\/strong> is less than 1 or greater than the number of rows in the table.<\/li>\n\n\n\n<li><strong>#REF!<\/strong>: Happens when the <strong>row_index_num<\/strong> exceeds the number of rows in the <strong>table_array<\/strong>.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Conclusion:<\/strong><\/h3>\n\n\n\n<p>Excel sheets are versatile tools that help users organize, analyze, and present data efficiently. For those just starting out, enrolling in an <a href=\"https:\/\/www.complianceprime.com\/blog\/2024\/02\/29\/excel-training-course-for-beginners-to-make-a-good-first-impression\/\">Excel training course for beginners<\/a> can be invaluable, as it teaches essential skills for working with <a href=\"https:\/\/www.complianceprime.com\/blog\/2020\/09\/25\/how-to-insert-an-excel-sheet-in-word\/\">Excel sheets<\/a>, formulas, and data analysis tools.The <strong>HLOOKUP<\/strong> formula is a powerful tool when dealing with data organized horizontally. By understanding the parameters and using it effectively, you can easily look up values and retrieve important information from large datasets.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The HLOOKUP (Horizontal Lookup) formula in Excel is used to search for a value in the top row of a table or range and return a value in the same&hellip;<\/p>\n","protected":false},"author":3,"featured_media":6020,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[6],"tags":[],"class_list":["post-6019","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-office"],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/www.complianceprime.com\/blog\/wp-json\/wp\/v2\/posts\/6019","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.complianceprime.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.complianceprime.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.complianceprime.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.complianceprime.com\/blog\/wp-json\/wp\/v2\/comments?post=6019"}],"version-history":[{"count":1,"href":"https:\/\/www.complianceprime.com\/blog\/wp-json\/wp\/v2\/posts\/6019\/revisions"}],"predecessor-version":[{"id":6021,"href":"https:\/\/www.complianceprime.com\/blog\/wp-json\/wp\/v2\/posts\/6019\/revisions\/6021"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.complianceprime.com\/blog\/wp-json\/wp\/v2\/media\/6020"}],"wp:attachment":[{"href":"https:\/\/www.complianceprime.com\/blog\/wp-json\/wp\/v2\/media?parent=6019"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.complianceprime.com\/blog\/wp-json\/wp\/v2\/categories?post=6019"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.complianceprime.com\/blog\/wp-json\/wp\/v2\/tags?post=6019"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}