{"id":573,"date":"2019-04-17T14:47:06","date_gmt":"2019-04-17T11:47:06","guid":{"rendered":"https:\/\/www.test4u.eu\/excelpedia\/?p=573"},"modified":"2019-05-06T12:55:34","modified_gmt":"2019-05-06T09:55:34","slug":"how-to-use-the-vlookup-function","status":"publish","type":"post","link":"https:\/\/www.test4u.eu\/excelpedia\/how-to-use-the-vlookup-function\/","title":{"rendered":"How to use the VLOOKUP function"},"content":{"rendered":"\n<p>When you start learning about the lookup functionality of Excel, <strong><a href=\"https:\/\/www.test4u.eu\/excelpedia\/the-vlookup-function\/\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"VLOOKUP  (opens in a new tab)\">VLOOKUP <\/a><\/strong>will probably be one of the first functions you will stumble upon.<\/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\/06o9wsJjRUE?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\/vlookup.xlsx\">vlookup<\/a><a href=\"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/vlookup.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 the <strong>VLOOKUP<\/strong> function.  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 gasoline 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 the solution.<\/p>\n\n\n\n<p>So in cell H14 we will\nuse the vlookup function to get the value we need.<\/p>\n\n\n\n<p>The vlookup function searches the data of the first column on the left of the cell area B4:D12 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 of the cell area. In our case the column is the second one and so the value is 15. <\/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 VLOOKUP 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 \u201ctable_array\u201d we need to define the cell area where Excel will perform the search; this is the cell area B4 to D12. At the argument \u201cCol_index_num\u201d we define the column number where the liters are displayed. In our case this is the second column, so we type the value 2. <\/p>\n\n\n\n<p>The last field\ncontains only two options: TRUE &amp; FALSE. We can type the number 1 or 0. If\nwe type the number 0, Excel will only look for an exact match. In case there is\nno such value, Excel will display an error message. If we type number 1 and\nthere is not an exact match, Excel will find the lower closest match in the\nfirst column. In our case it would be 75.<\/p>\n\n\n\n<p>We need to note that\nin order to use this function with the last attribute set to 1, the cell area\nneeds to be sorted in ascending order by the column in which we want to look\nfor the value. In our case by the column \u201cmiles\u201d. <\/p>\n\n\n\n<p>We type 0 and click at\nOK. As a result, the value 15 will be shown, which corresponds to 100 miles.<\/p>\n\n\n\n<p>If we change the value of the cell F14 to 110 miles, we can see that an error message appears because there aren\u2019t any values available for 110 miles. <\/p>\n\n\n\n<p>The letter V at\nVlookup stands for Vertical. So, VLOOKUP looks for a value, in our case 100, at\nthe first column of a cell area, and when it finds it, it returns the value\nthat is found at a cell with the same vertical position and on the right of the\nfirst column.<\/p>\n\n\n\n<p>In our example, we have asked for the second column, that\u2019s why we had the result 15. In case we had asked for the third column, we would have the result 19.5.<\/p>\n\n\n\n<div style=\"height:50px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>When you start learning about the lookup functionality of Excel, VLOOKUP will probably be one of the first functions you will stumble upon.<\/p>\n","protected":false},"author":2,"featured_media":575,"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,32,53],"class_list":["post-573","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","tag-lookup-functions","tag-reference","tag-vlookup"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/www.test4u.eu\/excelpedia\/wp-content\/uploads\/2019\/04\/vlookup.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/paRnIz-9f","_links":{"self":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/573","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=573"}],"version-history":[{"count":0,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/posts\/573\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media\/575"}],"wp:attachment":[{"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/media?parent=573"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/categories?post=573"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.test4u.eu\/excelpedia\/wp-json\/wp\/v2\/tags?post=573"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}