{"id":581,"date":"2019-04-17T15:19:01","date_gmt":"2019-04-17T12:19:01","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=581"},"modified":"2019-05-06T12:56:38","modified_gmt":"2019-05-06T09:56:38","slug":"how-to-use-the-lookup-function","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-lookup-function\/","title":{"rendered":"How to use the LOOKUP Function"},"content":{"rendered":"\n<p>From all the lookup functions, <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-lookup-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"LOOKUP  (opens in a new tab)\">LOOKUP<\/a><\/strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-lookup-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"LOOKUP  (opens in a new tab)\"> <\/a>is the least useful. Even though it is a powerful function, it is hardly used at all.<\/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\/bgSDXMpnaTQ?version=3&#038;rel=0&#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\/lookup.xlsx\">lookup<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/lookup.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>We will continue with\nthe lookup function. The lookup function has two forms. The Vector form and the\narray form.<\/p>\n\n\n\n<p>Microsoft proposes not to use the array form and use their two other lookup functions. <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><\/strong>and <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-hlookup-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"HLOOKUP (opens in a new tab)\">HLOOKUP<\/a><\/strong>.<\/p>\n\n\n\n<p>But in truth, although\nit is a powerful function, lookup is hardly used at all.<\/p>\n\n\n\n<p> In cell area B4 to D12 we can see the fuel consumption and the cost in dollars, for certain distances calculated in miles.<\/p>\n\n\n\n<p>For example, for a 75-mile\njourney we need 10 liters of fuel which cost 13 dollars.<\/p>\n\n\n\n<p>Let\u2019s suppose we want\nto look up the consumption in liters for a 100-mile journey and show it in cell\nH14. In such a small pool of data it is relatively easy to find the proper\nvalue and manually type it in the proper cell.<\/p>\n\n\n\n<p>Imagine if your data\nspanned to thousands of rows and columns. Finding out the needed value then,\ncould be impossible.<\/p>\n\n\n\n<p>This is where Excel\u2019s\nlookup functions provide us with a solution.<\/p>\n\n\n\n<p>In order for the\nlookup function to work properly in either of its forms the column with the\ndata we will be searching must be sorted in ascending order. Also the value we\nneed to search for cannot be lower than the lower value of this column, or an\nerror will occur.<\/p>\n\n\n\n<p>The lookup function searches the data of the first column on the left of the cell area B4:B12 for a value, in our case it is 100, and returns the value of the cell that can be found at the same row, and at a given column. In our case the column is column C and so the value is 15. <\/p>\n\n\n\n<p>So in cell H14 we will\nuse the vector form of the function to get the value we need.<\/p>\n\n\n\n<p>We select the cell and\nnavigate to the Formulas tab. We click at insert function and set the category\nto Lookup &amp; Reference. We locate the LOOKUP function and click OK. We can\nof course just type the name of the function after the equals sign.<\/p>\n\n\n\n<p>At the arguments window, we set the value that we are looking for at the \u201cLookup_value\u201d. We select the cell F14, which refers to the distance that we are looking for, the 100 miles. <\/p>\n\n\n\n<p>At the field \u201csearch_table\u201d we need to define the cell area where Excel will perform the search; this is the cell area B4 to B12. At the argument \u201cresults_table\u201d we define the column number where the liters are displayed. In our case this is column C. <\/p>\n\n\n\n<p>If we use the value of the cell F15 in cell H15, we can see that the result remains the same, because if an exact match is not found, the Lookup function will match the closest value below the lookup value.<\/p>\n\n\n\n<p>The array form of the\nfunction is a bit more complicated. It takes two attributes. The first is the\nlookup value and the second is a table of data containing values to be searched\nin the first row or column and values to be returned in its last row or column.<\/p>\n\n\n\n<p>Suppose we want to\napproximately calculate the cost of fuel for our monthly trips in cell K5 to\nK12.<\/p>\n\n\n\n<p>First we select the\ncell range K5:K12 and then we type the equals sign followed by the name of our\nfunction. The fist attribute is the lookup values which in our case is the cell\nrange B5:b12.<\/p>\n\n\n\n<p>We said that this form of lookup chooses its results from the last column of the given array. So since we want to show the cost for the given mileages, the last column of the given array should be column D. So we select B5:D12.<\/p>\n\n\n\n<p>We will use this as an\narray formula so we have to remember to press ctrl shift and enter instead of\njust enter.<\/p>\n\n\n\n<p>We can see that the\napproximate cost for each trip is shown in its proper place.<\/p>\n\n\n\n<p>If instead of cost, we needed to show the liter consumption then as the second attribute of the lookup function we would provide the cells B5:C12.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>From all the lookup functions, LOOKUP is the least useful. Even though it is a powerful function, it is hardly used at all.<\/p>\n","protected":false},"author":2,"featured_media":583,"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":[48,49,32],"class_list":["post-581","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-lookup","tag-lookup-functions","tag-reference"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/lookup.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-9n","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/581","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=581"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/581\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/583"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=581"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=581"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=581"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}