VLOOKUP
I would say, VLOOKUP function is one of the infamous function in Excel. I would say, almost 90% of Excel Power user, which is YOU will never feel complete not involving VLOOKUP in their job routine. Some people might say they use VLOOKUP to compare the older list with the newer list; Some might say they use VLOOKUP to extract information; Some may say they use VLOOKUP to search information; Some might say they use VLOOKUP to join or merge table etc.
Whichever way you use VLOOKUP, I will cover most of the ways of above mentioned. But first, you need to understand what VLOOKUP is.
VLOOKUP’s lil brother
Just in case you’re not aware, VLOOKUP has a twin brother called HLOOKUP. So, in what situation you will be needing that lil brother? That will depend on the Table that you’re looking up (or comparing) from.
If your Table is aligned vertically which is how typically a list would look like, you will be needing VLOOKUP, since the V in VLOOKUP stands for Vertical.
If your Table is aligned horizontally, you will be needing HLOOKUP, since the H in HLOOKUP stands for Horizontal.
Lookup mode
VLOOKUP and HLOOKUP each has 2 different mode while looking up for the data. First will be exact match mode while another one will be approximately match mode.
Exact Match mode usually used to lookup for identifier or ID, simply because identifier is usually unique. So, whenever the VLOOKUP found its one and only match from the lookup table, it will return results from the table. Some of you might curious about redundant ID. Sorry, VLOOKUP will only return result based of the first matched ID. But of course, if you wonder is there any solution to that. The answer is YES (I will cover that in a bit, not in this series).
Approximately Match mode is often used to lookup for number range. When this mode is turned ON and VLOOKUP is on its mission, it will look for the closest match number within the number list. In fact, it tries to lookup for the biggest number that is less than itself. In another form of explanation is, it uses “>=” comparison mode while searching for the closest matched number from the list. This will be covered in detail later on.
VLOOKUP is always RIGHT
How ? How come VLOOKUP is always right? This sis because VLOOKUP only look towards right. As mentioned, VLOOKUP is being used to extract information. But how does VLOOKUP know what to extract? You will have to tell VLOOKUP what to extract and based on what LookupValue. Once it has found its matched, it will begin to travel to right to extract whatever information that is located at the right side.
Which also means, the LookupValue must be able to be found in the first column of the table, after that it will goes right.
This is how I suggest your table should look like.
Some might tell me, “Isn’t as long the information that I would like to extract is located at the right side of the LookupValue will do?” Urm, I personally wouldn’t recommend that is because “I don’t wanna to miss a thing”. That song literally hit me the moment I wrote that.
My best suggestion is, if you do realize that the Lookup column is not being located in the first column, you should do everything you could to make that happen.
Start Counting 1..2..3..
When the “looking right” situation is going on, VLOOKUP is busy looking for which column should it be extracting as the output. This is also depending on what you would like to extract. If you are extracting the Product based on the Sales_ID, the count of the column index number will be 5; If you are extracting the Profit based on the Sales_ID, then the count will be 8 then.
HLOOKUP is always DOWN
Since the HLOOKUP will take the whole lookup experience in the horizontal way, so once it has found its matched, it will begin to look down for the extraction. Once it begins to look down, it will start counting the row index since it is going down.
HLOOKUP and VLOOKUP they are basically the same
So after a long winded explanation, can you find the similarity between these 2 functions? They are basically the same, but when to use who is simply based on how your table is being align and that’s it.
In the next article I’m covering how VLOOK can be used to solve some of the problem face.
