{"id":586,"date":"2019-04-17T15:31:22","date_gmt":"2019-04-17T12:31:22","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=586"},"modified":"2020-04-30T17:15:41","modified_gmt":"2020-04-30T14:15:41","slug":"advanced-lookup-functions-combinations-part-2","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/advanced-lookup-functions-combinations-part-2\/","title":{"rendered":"Advanced Lookup Functions Combinations: Part 2"},"content":{"rendered":"\n<p>We will continue our quest for the best lookup formula with another combination. We will use an <strong><a rel=\"noreferrer noopener\" aria-label=\"OFFSET (opens in a new tab)\" href=\"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-offset-function\/\" target=\"_blank\">OFFSET<\/a><\/strong> and two <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> functions to do a matrix lookup. Unfortunately, this formula also only searches from the right column to the left and from the top to the bottom, but is very easy to understand and use.<\/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\/FVdieX5qb78?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-2.xlsx\">advanced lookup 2<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/advanced-lookup-2.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><strong>Transcript<\/strong><\/p>\n\n\n\n<p>In this and the\nfollowing lesson, the knowledge of the simple syntax of the functions\nmentioned, is considered a perquisite.<\/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 cell J3 we type the offset function with reference cell the first cell of our table which is A1.<\/p>\n\n\n\n<p>We will use the first\nmatch function to find the row number and the second to find the column number.<\/p>\n\n\n\n<p>We type the name of the function, the lookup value in cell J2 and the lookup array (cells A2:A43). We are looking for an exact match. Then we continue to the next match function for the column number. We type the function again and the lookup value in cell J1. The lookup array is A1:H1 and we are again looking for an exact match.<\/p>\n\n\n\n<p>Take note here that to\ncalculate the right column number we have to subtract one from the result of\nthe match function since the reference cell of the offset function is part of\nthe match lookup array.<\/p>\n\n\n\n<p>The next two\nattributes of offset can be ignored since we are looking for a single cell.<\/p>\n\n\n\n<p>We have the required\nresult which is 40.<\/p>\n\n\n\n<p>This combination seems\na lot easier to understand and use from the ones we have seen so far, at least\nto me.<\/p>\n\n\n\n<p>But it has its\nlimitations. First we have to remember to subtract the number one from our\nmatch functions if the reference cell of the offset function is part of the\nlookup array of the match function, and second we cannot lookup from right to\nleft. We cannot look for example at what date we had a sale of 500 units. Not\nwithout needlessly complicating our formula at least.<\/p>\n\n\n\n<p>Luckily there is another way that removes all limitations and complications and seems, at least to me, a lot easier to understand. In the <strong><a rel=\"noreferrer noopener\" aria-label=\"following lesson (opens in a new tab)\" href=\"https:\/\/www.test4u.eu\/excelpedia\/advanced-lookup-functions-combinations-part-3\/\" target=\"_blank\">following lesson<\/a><\/strong> we will see the end of our quest for the best lookup function where we will explain how index and match can be combined to the best lookup formula in excel.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>We will continue our quest for the best lookup formula with another combination. We will use an OFFSET and two MATCH functions to do a matrix lookup. Unfortunately, this formula also only searches from the right column to the left and from the top to the bottom, but is very easy to understand and use.<\/p>\n","protected":false},"author":2,"featured_media":595,"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":[49,51,52],"class_list":["post-586","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-lookup-functions","tag-match","tag-offset"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/advlookup2.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-9s","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/586","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=586"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/586\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/595"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=586"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=586"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=586"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}