{"id":591,"date":"2019-04-17T15:35:13","date_gmt":"2019-04-17T12:35:13","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=591"},"modified":"2020-06-18T13:56:20","modified_gmt":"2020-06-18T10:56:20","slug":"advanced-lookup-functions-combinations-part-3","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/advanced-lookup-functions-combinations-part-3\/","title":{"rendered":"Advanced Lookup Functions Combinations: Part 3"},"content":{"rendered":"\n<p>We have reached the end of our quest for the best lookup function, where we will talk about the combination of <strong><a rel=\"noreferrer noopener\" aria-label=\"INDEX  (opens in a new tab)\" href=\"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-index-function\/\" target=\"_blank\">INDEX <\/a><\/strong>and <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-match-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"MATCH (opens in a new tab)\">MATCH<\/a><\/strong>. In the current lesson, we will use INDEX with dual MATCH functions, to achieve a matrix lookup functionality. But you can easily skip one of the two MATCH functions, and replace it with a static value if you are not interested in a matrix lookup.<\/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\/x7JW7EHayEQ?version=3&#038;rel=1&#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\/advanced-lookup-3.xlsx\">advanced lookup 3<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/advanced-lookup-3.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>The knowledge of the\nsimple syntax of the functions mentioned, is considered a prerequisite.<\/p>\n\n\n\n<p>We are trying to find\nthe number of units sold on January 20<sup>th<\/sup> of 2015.<\/p>\n\n\n\n<p>In J3 we type the name\nof the function, and the cell area of our data which is, A2:H43.<\/p>\n\n\n\n<p>For the row number, we\nwill use our first match function.<\/p>\n\n\n\n<p>We type match and set\nJ2 as the lookup value, and the data of the first column as the lookup array,\n(A2:A43). We are looking for the exact match so we type \u201c0\u201d.<\/p>\n\n\n\n<p>Next, for the column\nnumber, we use our second match function, with j1 as the lookup value and, A1:H1\nas the lookup array. <\/p>\n\n\n\n<p>We use 0 again, since\nwe are looking for an exact match. We close the parenthesis and press enter. <\/p>\n\n\n\n<p>The result, as\nexpected, is 40. Kind of easy, isn\u2019t it?<\/p>\n\n\n\n<p>The limitation of all\nthe other formulas for lookup is, that there is no easy way to do a right to\nleft search.<\/p>\n\n\n\n<p>Fortunately, this\ncombination doesn\u2019t suffer from the same disability.<\/p>\n\n\n\n<p>Right to left search\ncan be done as easily as left to right.<\/p>\n\n\n\n<p>Suppose we want to\nfind the date when we sold 500 units.<\/p>\n\n\n\n<p>We use the same syntax\nas before, in cell J16. First the index function with our data array, and then\nthe two match functions, for row and column number respectively.<\/p>\n\n\n\n<p>For the first, the\nlookup value is found on L15, which are the 500 units, and lookup area the\ncells F2:F43. We are looking for an exact match, so the last attribute is set\nto 0.<\/p>\n\n\n\n<p>For the column number,\nwe type the match function again, with first attribute, the cell J15, and\nlookup array the A1:H1 cell range. Once more, we search for an exact match. <\/p>\n\n\n\n<p>We close the\nparenthesis, and we have the required result.<\/p>\n\n\n\n<p>Until Microsoft\ncreates a built-in function, that matches or surpasses the versatility of index\nand match combination, I believe they are the best choice for any lookup\nscenario. <\/p>\n\n\n\n<p>This concludes the chapter for lookup functionality in Excel, and I hope It was helpful to you. Stick around, for equally interesting stuff.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>We have reached the end of our quest for the best lookup function, where we will talk about the combination of INDEX and MATCH. In the current lesson, we will use INDEX with dual MATCH functions, to achieve a matrix lookup functionality. But you can easily skip one of the two MATCH functions, and replace it with a static value if you are not interested in a matrix lookup.<\/p>\n","protected":false},"author":2,"featured_media":598,"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":[50,49,51],"class_list":["post-591","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-index","tag-lookup-functions","tag-match"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/advlookup3.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-9x","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/591","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=591"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/591\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/598"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=591"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=591"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=591"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}