{"id":3002,"date":"2016-05-09T16:57:42","date_gmt":"2016-05-09T14:57:42","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=3002"},"modified":"2024-02-06T16:03:04","modified_gmt":"2024-02-06T15:03:04","slug":"two-way-lookup-using-intersect-function-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/two-way-lookup-using-intersect-function-excel\/","title":{"rendered":"Two Way Lookup Using The SUM Intersect Function"},"content":{"rendered":"<p>There are various lookup functions that bring back values from a range of data like the <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/vlookup-function-introduction-excel\/\" target=\"_blank\" rel=\"noopener noreferrer\">VLOOKUP<\/a>, <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/index-function-introduction\/\" target=\"_blank\" rel=\"noopener noreferrer\">INDEX<\/a>, <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/how-to-use-index-match-formula-in-excel\/\" target=\"_blank\" rel=\"noopener noreferrer\">INDEX-MATCH<\/a> and <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-offset-function-introduced\/\" target=\"_blank\" rel=\"noopener noreferrer\">OFFSET<\/a>\u00a0functions,\u00a0just to name a few.<\/p>\n<p>There is another lookup <a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"formula\" data-wpil-keyword-link=\"linked\">formula<\/a>\u00a0that you can use that will\u00a0return\u00a0the intersection of two Named Ranges!<\/p>\n<p>This is done by using the <strong>SUM<\/strong> formula and entering a Named Range for the 1st argument and then pressing the SPACE BAR on your keyboard (thus activating the intersection) and entering a 2nd Named Range.<\/p>\n<p>This combination will look at the intersection of the 1st Named Range and the 2nd Named Range and return that cell&#8217;s value. \u00a0I show you how below&#8230;<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/kgCZPW8_viU?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><\/p>\n<p><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><br \/>\n<a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/Sum-Interesction.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<\/span><span class=\"postTitle px-3\">Sum-Interesction.xlsx<\/span><\/a><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong><\/span> <strong>Highlight your data<\/strong>\u00a0which has to have Column headings and Row headings, just like the table below:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/highlight-data-1.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Two Way Lookup Using The SUM Intersect Function\"  class=\"alignnone size-full wp-image-3003\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/highlight-data-1.jpg\" alt=\"Two Way Lookup Using The SUM Intersect Function\" width=\"614\" height=\"189\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/highlight-data-1.jpg 614w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/highlight-data-1-300x92.jpg 300w\" sizes=\"(max-width: 614px) 100vw, 614px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span> Go to the ribbon menu and select <em><strong><a class=\"wpil_keyword_link\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/top-excel-formulas-function-examples-to-get-better-at-microsoft-excel\/\" target=\"_blank\" rel=\"noopener\" title=\"Formulas\" data-wpil-keyword-link=\"linked\">Formulas<\/a> &gt; Create from Selection\u00a0<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/create-from-selection.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Two Way Lookup Using The SUM Intersect Function\"  class=\"alignnone size-full wp-image-3004\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/create-from-selection.jpg\" alt=\"Two Way Lookup Using The SUM Intersect Function\" width=\"439\" height=\"127\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/create-from-selection.jpg 439w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/create-from-selection-300x87.jpg 300w\" sizes=\"(max-width: 439px) 100vw, 439px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong><\/span>\u00a0This will bring up the<em> Create Names from Selection<\/em> dialogue box. \u00a0<strong>Choose the <em>Top Row<\/em> and<em> Left Column<\/em>\u00a0boxes<\/strong>, as this is where your headings are located and press <strong>OK<\/strong>:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/create-names-dialogue-box.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Two Way Lookup Using The SUM Intersect Function\"  class=\"alignnone size-full wp-image-3005\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/create-names-dialogue-box.jpg\" alt=\"Two Way Lookup Using The SUM Intersect Function\" width=\"260\" height=\"173\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4:<\/strong><\/span>\u00a0In the ribbon menu, go to <em><strong>Formulas &gt; Name Manager<\/strong> <\/em>to see the Named Ranges that were created in<strong> Step 3<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/Name-Manager.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Two Way Lookup Using The SUM Intersect Function\"  class=\"alignnone size-full wp-image-3006\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/Name-Manager.jpg\" alt=\"Two Way Lookup Using The SUM Intersect Function\" width=\"552\" height=\"432\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/Name-Manager.jpg 552w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/Name-Manager-300x235.jpg 300w\" sizes=\"(max-width: 552px) 100vw, 552px\" \/><\/a><\/p>\n<p>You can<strong> double click on a Named Range<\/strong> and click on the<strong> Refers to area<\/strong> to see\u00a0the range (press ESC to get out of this screen):<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/named-range-confirm.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Two Way Lookup Using The SUM Intersect Function\"  class=\"alignnone size-full wp-image-3007\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/named-range-confirm.jpg\" alt=\"Two Way Lookup Using The SUM Intersect Function\" width=\"620\" height=\"495\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/named-range-confirm.jpg 620w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/named-range-confirm-300x240.jpg 300w\" sizes=\"(max-width: 620px) 100vw, 620px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 5:<\/strong><\/span> <strong>Enter the SUM function<\/strong> and in the first argument type in\u00a0the<strong> 1st Named Range<\/strong>:<\/p>\n<h2 style=\"text-align: center;\">=SUM(<span style=\"color: #0000ff;\">EUROPE<\/span><\/h2>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/sum-1st-argument.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Two Way Lookup Using The SUM Intersect Function\"  class=\"alignnone size-full wp-image-3008\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/sum-1st-argument.jpg\" alt=\"Two Way Lookup Using The SUM Intersect Function\" width=\"779\" height=\"240\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/sum-1st-argument.jpg 779w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/sum-1st-argument-300x92.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/sum-1st-argument-768x237.jpg 768w\" sizes=\"(max-width: 779px) 100vw, 779px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 6:<\/strong><\/span> <strong>Enter a SPACE<\/strong> after the 1st argument. \u00a0Now type in\u00a0the <strong>2nd Named Range<\/strong>:<\/p>\n<h2 style=\"text-align: center;\">=SUM(<span style=\"color: #0000ff;\"><span style=\"color: #000000;\">EUROPE<\/span> MAR)<\/span><\/h2>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/2nd-argument.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Two Way Lookup Using The SUM Intersect Function\"  class=\"alignnone size-full wp-image-3009\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/2nd-argument.jpg\" alt=\"Two Way Lookup Using The SUM Intersect Function\" width=\"851\" height=\"239\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/2nd-argument.jpg 851w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/2nd-argument-300x84.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/2nd-argument-768x216.jpg 768w\" sizes=\"(max-width: 851px) 100vw, 851px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>This will return the intersection of the 1st and 2nd Named Ranges:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/result.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Two Way Lookup Using The SUM Intersect Function\"  class=\"alignnone size-full wp-image-3010\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/result.jpg\" alt=\"Two Way Lookup Using The SUM Intersect Function\" width=\"779\" height=\"239\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/result.jpg 779w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/result-300x92.jpg 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/result-768x236.jpg 768w\" sizes=\"(max-width: 779px) 100vw, 779px\" \/><\/a><\/p>\n<p>You can even make this formula interactive by\u00a0inserting a <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/drop-down-list-with-data-validation\/\" target=\"_blank\" rel=\"noopener noreferrer\">drop down menu <\/a>(data validation) for the Months and the Regions!<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are various lookup functions that bring back values from a range of data like the VLOOKUP, INDEX, INDEX-MATCH and OFFSET\u00a0functions,\u00a0just to name a few. There is another lookup formula\u00a0that you can use that will\u00a0return\u00a0the intersection of two Named Ranges! This is done by using the SUM formula and entering a Named Range for the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17427,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[11,3,282],"tags":[43,79],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/3002"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=3002"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/3002\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17427"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=3002"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=3002"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=3002"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}