{"id":7958,"date":"2018-04-20T07:19:32","date_gmt":"2018-04-20T07:19:32","guid":{"rendered":"https:\/\/www.nurturetechacademy.in\/?p=7958"},"modified":"2018-04-20T07:19:32","modified_gmt":"2018-04-20T07:19:32","slug":"use-vlookup-multiple-tables","status":"publish","type":"post","link":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/use-vlookup-multiple-tables\/","title":{"rendered":"How to use VLOOKUP with multiple tables"},"content":{"rendered":"<p>Hi,<\/p>\n<p>As an excel user, when we are maintaining data on different tables or on different sheets however we want to pull the information on a single sheet from the tables. If we do type manually or copy and paste it, it will consume more time basically Its time taken process.<!--more--><\/p>\n<p>Let\u2019s understand the problems first then we will discuss the solution in detail-<\/p>\n<p><strong><span style=\"text-decoration: underline;\">The Problem-<\/span><\/strong><\/p>\n<p>Below is an example with a dummy data-<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-7959 aligncenter\" src=\"https:\/\/www.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image1.png\" alt=\"Image1\" width=\"606\" height=\"393\" srcset=\"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image1.png 606w, https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image1-600x389.png 600w, https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image1-300x195.png 300w\" sizes=\"auto, (max-width: 606px) 100vw, 606px\" \/><\/p>\n<p>There are three tables with different Commission rate e.g. Table HDD, Table MOUSE, Table Monitor<em>.<\/em><\/p>\n<p>We want to get commission rate according to their Unit Sold given in second column of Excel sheet.<\/p>\n<p><span style=\"text-decoration: underline;\"><strong>Step-1<\/strong><\/span><\/p>\n<p>In the example shown, there are three tables, so we have to define a Name with the help of Naming range option to each table.<\/p>\n<p>How to define Naming range on table?<\/p>\n<p>Select the Table-&gt;Go to Formulas tab-&gt; Define Name-&gt; Define Name in Name Box-&gt; Ok<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-7962 aligncenter\" src=\"https:\/\/www.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image-2-2.jpg\" alt=\"Image-2\" width=\"1226\" height=\"690\" srcset=\"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image-2-2.jpg 1226w, https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image-2-2-600x338.jpg 600w, https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image-2-2-300x169.jpg 300w, https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image-2-2-768x432.jpg 768w, https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image-2-2-1024x576.jpg 1024w\" sizes=\"auto, (max-width: 1226px) 100vw, 1226px\" \/><\/p>\n<p>Repeat the process for another table and define Name i.e. Mouse, Monitor<\/p>\n<p><em>Note: It would be good, give Name that should be like your product name so that it\u2019s easy to find the value.<\/em><\/p>\n<p><span style=\"text-decoration: underline;\"><strong>Step-2<\/strong><\/span><\/p>\n<p>At the core, this is a standard VLOOKUP formula. The only difference is the use of INDIRECT to return a valid table array. INDIRECT function picks up the text in A2 (&#8220;HDD&#8221;) and resolves it the named range HDD, which resolves to G2:H6, which is returned to VLOOKUP.<\/p>\n<p>Open your worksheet-&gt;type formula in column C2 i.e. \u201c=VLOOKUP (B2, Indirect(A2),2,1)<\/p>\n<p>What VLOOKUP function says?<\/p>\n<ul>\n<li>Value &#8211; The value to look for in the first column of a table i.e. B2 (Unit Sold)<\/li>\n<li>Table &#8211; The table from which to retrieve a value, however when we drag the formula down we need to keep change the table area according to product name, So we use Indirect function-<\/li>\n<\/ul>\n<p>What does the \u201cIndirect\u201d function do?<\/p>\n<p>The INDIRECT function is useful when you want to return a value, based on a text string.<\/p>\n<ul>\n<li>Col_index &#8211; The column in the table from which to retrieve a value.<\/li>\n<li>Range_lookup &#8211; TRUE = approximate match (default). FALSE = exact match.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-7963\" src=\"https:\/\/www.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image-3.jpg\" alt=\"Image-3\" width=\"1246\" height=\"701\" srcset=\"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image-3.jpg 1246w, https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image-3-600x338.jpg 600w, https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image-3-300x169.jpg 300w, https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image-3-768x432.jpg 768w, https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/uploads\/2018\/04\/Image-3-1024x576.jpg 1024w\" sizes=\"auto, (max-width: 1246px) 100vw, 1246px\" \/><\/p>\n<p>Note: &#8211; The purpose of this formula is to allow an easy way to switch between table range inside a VLOOKUP formula. However, if you just try to give VLOOKUP a table array in the form of table (i.e \u201cHDD) the formula will give you error. The indirect function is needed to resolve the test to a valid reference.<\/p>\n<p>Happy Learning ?<\/p>\n<p>For any advance excel training in corporate or for individual query, kindly\u00a0<a href=\"https:\/\/www.nurturetechacademy.in\/\">contact us.<\/a><\/p>\n<p>&nbsp;<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_7958\" class=\"pvc_stats all  \" data-element-id=\"7958\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Hi,<br \/>\nAs an excel user, when we are maintaining data on different tables or on different sheets however we want to pull the information on a single sheet from the tables. If we do type manually or copy and paste <\/p>\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_7958\" class=\"pvc_stats all  \" data-element-id=\"7958\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"author":1,"featured_media":7964,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"footnotes":""},"categories":[6,56],"tags":[265,266,263,243,264,257,260,259,249,256,248,244,250,252,245,253,254,246,251,267,255,262,258,247,261],"class_list":["post-7958","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-excel","tag-corporate-training-companies","tag-corporate-training-companies-in-delhi","tag-corporate-training-courses","tag-corporate-training-in-delhi","tag-corporate-training-programs","tag-excel-course-online","tag-excel-courses-online","tag-excel-online-course","tag-excel-online-training","tag-excel-training-in-delhi","tag-excel-training-online","tag-learn-microsoft-excel","tag-learn-ms-excel-online","tag-learning-excel-online","tag-learning-microsoft-excel","tag-microsoft-excel-course","tag-microsoft-excel-online-training","tag-microsoft-excel-training","tag-ms-excel-learning","tag-ms-excel-online-training","tag-ms-excel-training-in-delhi","tag-online-corporate-training","tag-online-excel-course","tag-online-excel-training","tag-online-excel-training-courses"],"_links":{"self":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/7958","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/comments?post=7958"}],"version-history":[{"count":2,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/7958\/revisions"}],"predecessor-version":[{"id":7966,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/7958\/revisions\/7966"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media\/7964"}],"wp:attachment":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media?parent=7958"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/categories?post=7958"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/tags?post=7958"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}