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

Extracting information using VLOOKUP()-Exact Match mode<\/h3>\n\n\n\n

I believe many people have heard of what VLOOKUP is and most of the time when I throw this question to my students \u201chow often do you use VLOOKUP?\u201d, some of them will tell me \u201cWhen I\u2019m in the loo, all I think about is VLOOKUP\u201d. *awkward* but Yes, as you can see, this is how attached they are with VLOOKUP, I can see you are one of em too..<\/p>\n\n\n\n

Anyways\u2026long story short, in this section here, I\u2019m going to explain how you can extract information using VLOOKUP.<\/p>\n\n\n\n

But first, you gotta provide enough information for VLOOKUP do get its job done. Without those proper information, it\u2019s like \u201crubbish in, rubbish out\u201d.<\/p>\n\n\n\n

What sorts of information should you provide in order to make sure it function as how the way you wanted it?<\/p>\n\n\n\n

Scenario 1:<\/strong><\/p>\n\n\n\n

A customer has sent a request to our customer service centre, request for the quotation according to the items she wanted to purchase.<\/p>\n\n\n\n

The letter:<\/p>\n\n\n\n

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

And now, according to all the item code listed in the email above, you will have to add in the Description<\/strong>, Unit Price<\/strong>, Subtotal<\/strong> and Grand Total Payable Amount <\/strong>to complete the whole quotation as shown in the table below. Your duty is to fill in the blank.<\/p>\n\n\n\n

Item Code<\/strong><\/td>Product Desc<\/strong><\/td>Qty<\/strong><\/td>Price Per Unit<\/strong><\/td>Subtotal<\/strong><\/td><\/tr>
M6404<\/td>??<\/td>255<\/td>??<\/td>??<\/td><\/tr>
C2852<\/td>??<\/td>300<\/td>??<\/td>??<\/td><\/tr>
M4789<\/td>??<\/td>686<\/td>??<\/td>??<\/td><\/tr>
C8156<\/td>??<\/td>2000<\/td>??<\/td>??<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Total Payable<\/strong><\/td>??<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

The extraction of most of the information above will be extracted by using VLOOKUP().<\/p>\n\n\n\n

Syntax of VLOOKUP()<\/h3>\n\n\n\n

The syntax of VLOOKUP is as below<\/p>\n\n\n\n

=VLOOKUP(Lookup_Value, Table_Array, Col_Index_Num, [Range_Lookup])<\/strong><\/p>\n\n\n\n

Lookup_Value<\/strong><\/p>\n\n\n\n

First of all, you will have to provide an ID<\/strong> (AKA identifier which is often unique\/ one and only one). With this ID, VLOOKUP will use it to match with all the item found in the first column of the database. And REMEMBER !!!<\/strong> the ID must be found in the first column of the database.<\/p>\n\n\n\n

According to our scenario above, the ID will be the Product Code<\/strong>.<\/p>\n\n\n\n

Table_Array<\/strong><\/p>\n\n\n\n

Table array will be the list\/table that you are going to extract the information from. In this case, the whole product database is located from in another worksheet. I will always recommend anyone who\u2019s dealing with functions and formula, if can, convert the database table or the facts table into Excel Table Object form. This will make the selection easier, at the meantime, as the table has expanded, the formula will update based on the latest size of the table (whether grow or shrink in size).<\/p>\n\n\n\n

Converting a range into a table, this is what you should do.<\/p>\n\n\n\n

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

Are you aware that I\u2019ve change the name of the table from the default name Table 1 to candy? That is to make out selection or reference towards the table become easier. You will see that in a bit.<\/p>\n\n\n\n

The Table_Array that we fill in will be Candy<\/strong>.<\/strong><\/p>\n\n\n\n

Col_Index_Num<\/strong><\/p>\n\n\n\n

Column index number will be the index of the column of which content you wish to extract from the table. As mentioned, you are going to extract the Product Desc and the Unit Price from the Candy <\/strong>table. The index starts from 1 which it the column of Product Code<\/strong>. If Product Code\u2019s column index is 1, which also make the index for the Product Desc- 2<\/strong> while the Unit Price- 3<\/strong>.<\/p>\n\n\n\n

Range Lookup<\/strong><\/p>\n\n\n\n

This is always at FALSE whenever your lookup value is a unique value which is Exact Match lookup mode. You can also use 0 to represent it.<\/p>\n\n\n\n

Put it all together<\/h3>\n\n\n\n

Product Description<\/strong><\/em><\/p>\n\n\n\n

Lookup Value                      : M6404<\/p>\n\n\n\n

Table                                      : Candy<\/p>\n\n\n\n

Column Index Num            : 2<\/p>\n\n\n\n

Range Lookup                      : False<\/p>\n\n\n\n

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

Price Per Unit<\/strong><\/em><\/p>\n\n\n\n

Lookup Value                      : M6404<\/p>\n\n\n\n

Table                                      : Candy<\/p>\n\n\n\n

Column Index Num            : 3<\/p>\n\n\n\n

Range Lookup                      : False<\/p>\n\n\n\n

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

Subtotal<\/strong><\/em><\/strong><\/p>\n\n\n\n

Subtotal                                :Qty * Price Per Unit<\/p>\n\n\n\n

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

Total Payable<\/strong><\/em><\/p>\n\n\n\n

Total Payable                       : Sum(All Subtotal)<\/p>\n\n\n\n

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

Slow processing<\/h3>\n\n\n\n

Good news fellow Excel Pro users! I\u2019ve been complaining the slow performance after having too many VLOOKUP function going on in a file. In the latest release of update for those who are using Office 365 Pro suite or Excel 2019, this issues had been fixed and it is now smooth like a baby arse!<\/p>\n\n\n\n

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

Check out more here<\/a> No more worries while looking up upon million rows of data anymore!<\/p>\n\n\n\n

Yay !!!!! Big shout out to Microsoft fam !!!!!. #awesomeimprovement<\/p>\n","protected":false},"excerpt":{"rendered":"

Extracting information using VLOOKUP()-Exact Match mode I believe many people have heard of what VLOOKUP is and most of the time when I throw this question to my students \u201chow often do you use VLOOKUP?\u201d, some of them will tell me \u201cWhen I\u2019m in the loo, all I think about is VLOOKUP\u201d. *awkward* but Yes, […]<\/p>\n","protected":false},"author":4,"featured_media":243,"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-818","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\/818","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=818"}],"version-history":[{"count":1,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/818\/revisions"}],"predecessor-version":[{"id":819,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/818\/revisions\/819"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media\/243"}],"wp:attachment":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media?parent=818"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/categories?post=818"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/tags?post=818"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}