{"id":811,"date":"2023-06-19T14:52:00","date_gmt":"2023-06-19T06:52:00","guid":{"rendered":"https:\/\/www.info-trek.com\/blog\/?p=811"},"modified":"2024-05-02T12:10:18","modified_gmt":"2024-05-02T04:10:18","slug":"vlookup-101-part-5","status":"publish","type":"post","link":"https:\/\/www.info-trek.com\/blog\/vlookup-101-part-5\/","title":{"rendered":"VLOOKUP 101 PART 5"},"content":{"rendered":"\n

Merging tables using Vlookup<\/h3>\n\n\n\n

Combine information from tables<\/em><\/p>\n\n\n\n

Combining tables was is a very day-to-day task that mostly Excel Pro User has been doing, for ages! This is simply because they are trying to retrieve information from another table and join it into a complete table. Sometimes they do that is also because they\u2019re trying to cross checking data from another table.<\/p>\n\n\n\n

This usually happened in between a transaction table or some maybe calling them the facts table and database table (or db table) \u201cWell there are so many ways of how people calling these tables\u2026I can go oon for 3 days if I were to list down all of em\u201d.<\/em> So, what\u2019s the difference in between these facts table and db table?<\/p>\n\n\n\n

If you would like to have the in-depth understanding towards the facts table, read this.<\/p>\n\n\n\n

\nhttps:\/\/en.wikipedia.org\/wiki\/Fact_table\n<\/div><\/figure>\n\n\n\n

But, I will simply explain it in a more layman term. When I bring up the facts table in the classroom are, I will simply tell my students\u2026Facts table are like activity table, every time when there is some sort of activity going on, it will be added to the facts table<\/p>\n\n\n\n

Let\u2019s take the factSales table as an example. Every time when a sales representative successfully closed a deal with the customer, this will then being recorded as a new line in the facts table. So first, the system will automatically generate a unique Sales ID<\/strong> (as most of the time this ID is a running number), then follow by the Date Time<\/strong> (when does it happened), the Customer ID<\/strong> (A unique code that represent the customer), the Staff ID<\/strong> (Usually is the ID of the sales representative who seal the deal) then the Amount<\/strong> (which is the sales figure).<\/p>\n\n\n\n

Sample of fact sales transaction:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

And you should expect this list will keep on expanding when there are more sales activity.<\/p>\n\n\n\n

If you look into this fact sales table, it has a lot of missing information. Example, When I look at the Customer ID 1225A-O896, you might not know who this is. Now you will be needing the db table which some might call it as the dimension table. So, dimension table usually are very informative, and it should contain every information you need. It is usually very wordy, filled with descriptions etc.<\/p>\n\n\n\n

An example that I\u2019m going to talk about is the dbCustomer table. This table will lead by the very first column the Customer ID<\/strong> column ( the unique id that assign to each customer to represent them), then follow by the Customer Name<\/strong> (the friendly name of the customer), Contact Person<\/strong> (the contact or the in-charge person of that customer), the Email<\/strong> (method to reach out to the customer), Country<\/strong> (which region or country that the customer is being located) etc.<\/p>\n\n\n\n

Sample of dbCustomer:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Alright, back to our story, now I needed some information from this dbCustomer table to be merged with the factSales table. The information that I need to be merged will be the Customer Name and the Country.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

In order to get this mission complete, I will have the VLOOKUP function being setup in the factSales Table, then extract the information from the dbCutomer table.<\/p>\n\n\n\n

To do that, I will first convert dbCustomer range into Table Object then rename the table to \u201cdbCustomer\u201d.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now after the table conversion, this will make your selection of the table easier when it comes to VLOOKUP.<\/p>\n\n\n\n

The vlookup is going to take the Customer ID from the factSales Table compared with the Customer ID from the dbCustomer Table. Once it has found its match, I will then extract the Customer Name and Country from the dbCustomer and return as output in the factSales Table.<\/p>\n\n\n\n

Syntax of VLOOKUP:<\/p>\n\n\n\n

=VLOOKUP(LookupValue, TableArray, ColumnIndexNumber, RangeType)<\/p>\n\n\n\n

LookupValue \u2013 1225A-O896<\/strong> < VLOOKUP will use this ID to match with the Customer ID column from dbCustomer Table.<\/p>\n\n\n\n

TableArray \u2013dbCustomer<\/strong> < The table you would like to extract information from<\/p>\n\n\n\n

ColumnIndexNumber \u20132<\/strong> < Which column of information you would like VLOOKUP to extract (it has to be in number form)<\/p>\n\n\n\n

RangeType \u2013FALSE<\/strong> < FALSE: Exact Match; TRUE: Approximately Match<\/p>\n\n\n\n

The complete version of the formula (Customer Name):<\/strong><\/p>\n\n\n\n

=VLOOKUP(1225A-O896, dbCustomer, 2, False)<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

If you\u2019re going to extract the Country column, simply switch the column index number from 2 to 5. This is because Country is being located at the 5th<\/sup> column of the dbCustomer table.<\/p>\n\n\n\n

The complete version of the formula (Country):<\/strong><\/p>\n\n\n\n

=VLOOKUP(1225A-O896, dbCustomer, 5, False)<\/p>\n","protected":false},"excerpt":{"rendered":"

Merging tables using Vlookup Combine information from tables Combining tables was is a very day-to-day task that mostly Excel Pro User has been doing, for ages! This is simply because they are trying to retrieve information from another table and join it into a complete table. Sometimes they do that is also because they\u2019re trying […]<\/p>\n","protected":false},"author":4,"featured_media":427,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-811","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/811","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/comments?post=811"}],"version-history":[{"count":1,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/811\/revisions"}],"predecessor-version":[{"id":812,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/811\/revisions\/812"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media\/427"}],"wp:attachment":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media?parent=811"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/categories?post=811"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/tags?post=811"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}