{"id":585,"date":"2019-04-17T15:24:55","date_gmt":"2019-04-17T12:24:55","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=585"},"modified":"2020-04-22T18:16:36","modified_gmt":"2020-04-22T15:16:36","slug":"advanced-lookup-functions-combinations-part-1","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/advanced-lookup-functions-combinations-part-1\/","title":{"rendered":"Advanced Lookup Functions Combinations: Part 1"},"content":{"rendered":"\n<p>We will begin a quest to find the <strong>best lookup function<\/strong>. All the internal functions of Excel have limitations which we will try to overcome.  In this and the following tutorials, the knowledge of the simple syntax of the functions mentioned (<strong><a rel=\"noreferrer noopener\" aria-label=\"VLOOKUP (opens in a new tab)\" href=\"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-vlookup-function\/\" target=\"_blank\">VLOOKUP<\/a>, <a rel=\"noreferrer noopener\" aria-label=\"HLOOKUP (opens in a new tab)\" href=\"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-hlookup-function\/\" target=\"_blank\">HLOOKUP<\/a>, <a rel=\"noreferrer noopener\" aria-label=\"MATCH (opens in a new tab)\" href=\"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-match-function\/\" target=\"_blank\">MATCH<\/a>)<\/strong>, is considered a requirement.<\/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\/12XRljQepqs?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-1.xlsx\">advanced lookup 1<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/advanced-lookup-1.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 basic limitation of VLOOKUP and HLOOKUP is that they are single dimensional. They only search vertically or horizontally. The most obvious way to overcome this is by combining the two of them in a single formula.<\/p>\n\n\n\n<p>In short we will try\nto make vlookup search in a matrix (both vertically and horizontally). To do\nthis we will use a vlookup function with a nested hlookup to provide us with\nthe column number attribute of the vlookup.<\/p>\n\n\n\n<p>We will need to add a\nrow of column numbers just below the headers of our columns as we can see on\nthe current sheet.<\/p>\n\n\n\n<p>In cell J we will look for the number of units sold on January 20 2015.<\/p>\n\n\n\n<p>We have added row 2\nwhich gives the number of each column.<\/p>\n\n\n\n<p>We type our function using the value in J2 as the lookup value and the cell area A3:H44 as our lookup array. For the number of column, we will use the hlookup function.<\/p>\n\n\n\n<p>We type the function and as lookup value we set the cell J1, and as lookup array the area A1:H2. We need the second row of data and of course the exact match. We need the exact match for the vlookup function as well. We press enter and there we have it a matrix lookup function using vlookup and lookup.<\/p>\n\n\n\n<p>Although this\ncombination works it needs us to create a new row with the column numbers. We\ncan easily avoid that by using the match function instead of hlookup to look\nfor the column number.<\/p>\n\n\n\n<p>So we reached our next\ncombination of functions Vlookup and Match.<\/p>\n\n\n\n<p>On our next sheet we see the same data minus the extra row. We are going to try to achieve the same thing as before. In cell J3 we type the vlookup function with the same attributes as before but instead of hlookup for column we use the match function. We use the value in J1 as lookup value and the range A1:H1 as lookup array. We are still searching for the exact match so we type 0 for both functions and when we press enter we have the same result as before. A lot more elegant don\u2019t you think?<\/p>\n\n\n\n<p>One other limitation\nof vlookup is that it can only search for values to the right of the first\ncolumn of its lookup array.<\/p>\n\n\n\n<p>So if we wanted to\nsearch for the date that the units sold were 500 it would be impossible. <\/p>\n\n\n\n<p>Choose gives us a kind of complicated way to achieve just that. We will stay on the current sheet and in cell J20.<\/p>\n\n\n\n<p>We type the vlookup\nfunction, with lookup value of 500 and to define the lookup array we will use\nchoose.<\/p>\n\n\n\n<p>This syntax of choose\nuses as first attribute an array. This means that we want choose to select from\nthe array variables all those with index in the given array in the first\nattribute. <\/p>\n\n\n\n<p>Let\u2019s type the\nfunction and try to explain it in plain language. We have given an array of two\nnumbers as the first attribute. This means that we want to choose both variable\nnumber 1 and number 2 from the variables given.<\/p>\n\n\n\n<p>The array variables\ngiven are the two columns. The one of units and the one of dates. Take note\nthat as first column we have set the column of units and second the column of\ndates. So as far as vlookup is concerned it still searches to the right since\nthe two column array we provided it with has the column units on the left and\nthe column date on the right. We will need the second column in our vlookup\nfunction of course and the exact match. The result is what we would expect and\nvlookup has a new functionality.<\/p>\n\n\n\n<p>We might be able to accomplish this for a matrix lookup as well but I wouldn\u2019t even try it. There are a lot better ways to achieve this and we will talk about them in our <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/advanced-lookup-functions-combinations-part-2\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"next lessons (opens in a new tab)\">next lessons<\/a><\/strong>.<\/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 begin a quest to find the best lookup function. All the internal functions of Excel have limitations which we will try to overcome.  In this and the following tutorials, the knowledge of the simple syntax of the functions mentioned (VLOOKUP, HLOOKUP, MATCH), is considered a requirement.<\/p>\n","protected":false},"author":2,"featured_media":589,"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,51,53],"class_list":["post-585","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-hlookup","tag-lookup-functions","tag-match","tag-vlookup"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/advlookup1.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-9r","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/585","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=585"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/585\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/589"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=585"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=585"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=585"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}