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 “how often do you use VLOOKUP?”, some of them will tell me “When I’m in the loo, all I think about is VLOOKUP”. *awkward* but Yes, as you can see, this is how attached they are with VLOOKUP, I can see you are one of em too..
Anyways…long story short, in this section here, I’m going to explain how you can extract information using VLOOKUP.
But first, you gotta provide enough information for VLOOKUP do get its job done. Without those proper information, it’s like “rubbish in, rubbish out”.
What sorts of information should you provide in order to make sure it function as how the way you wanted it?
Scenario 1:
A customer has sent a request to our customer service centre, request for the quotation according to the items she wanted to purchase.
The letter:
And now, according to all the item code listed in the email above, you will have to add in the Description, Unit Price, Subtotal and Grand Total Payable Amount to complete the whole quotation as shown in the table below. Your duty is to fill in the blank.
| Item Code | Product Desc | Qty | Price Per Unit | Subtotal |
| M6404 | ?? | 255 | ?? | ?? |
| C2852 | ?? | 300 | ?? | ?? |
| M4789 | ?? | 686 | ?? | ?? |
| C8156 | ?? | 2000 | ?? | ?? |
| Total Payable | ?? |
The extraction of most of the information above will be extracted by using VLOOKUP().
Syntax of VLOOKUP()
The syntax of VLOOKUP is as below
=VLOOKUP(Lookup_Value, Table_Array, Col_Index_Num, [Range_Lookup])
Lookup_Value
First of all, you will have to provide an ID (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 !!! the ID must be found in the first column of the database.
According to our scenario above, the ID will be the Product Code.
Table_Array
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’s 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).
Converting a range into a table, this is what you should do.
Are you aware that I’ve 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.
The Table_Array that we fill in will be Candy.
Col_Index_Num
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 table. The index starts from 1 which it the column of Product Code. If Product Code’s column index is 1, which also make the index for the Product Desc- 2 while the Unit Price- 3.
Range Lookup
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.
Put it all together
Product Description
Lookup Value : M6404
Table : Candy
Column Index Num : 2
Range Lookup : False
Price Per Unit
Lookup Value : M6404
Table : Candy
Column Index Num : 3
Range Lookup : False
Subtotal
Subtotal :Qty * Price Per Unit
Total Payable
Total Payable : Sum(All Subtotal)
Slow processing
Good news fellow Excel Pro users! I’ve 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!
Check out more here No more worries while looking up upon million rows of data anymore!
Yay !!!!! Big shout out to Microsoft fam !!!!!. #awesomeimprovement
