Excel vlookup is most talked about inbuilt function available with MS excel or Libre office. This is one of the function that is definitively required if you are a working professional and have to work on some sort of spreadsheet containing data. However, there are many of us who do not have a clear understanding of the function and hence are not able to utilize this function. Often, we shy away because we do not have an understanding of this powerful function and we prefer to work manually spending hours.
In this post, we will try to understand vlookup function the easiest way. Once you have gone through this article and develop the understanding of how vlookup function is designed and how to use it, do not forget to practices it multiple time to make things clarified further.
Vlookup explained – vlookup simply means vertical lookup. That literally means that the function will lookup for a particular value vertically.
Let us understand this with an example. In the figure below, Social security Number is arranged vertically in a column. What if you need to find a social security number as 917823098 in this list? You will start from the first cell (A2) and will move vertically downwards until you match (Cell A5). Please note how you started from beginning of the data and moved vertically downwards each cell to match your SSN (917823098) in cell A5.
Wow!! That’s seems quite simple. Right?
Yes. Now that we have basic understanding of how vlookup function searches the data vertically for a match, lets take a look at the formula and its output. We will take the same example and will try to figure out if SSN 91783098 can be found using a formula? Remember, we matched the SSN manually; hence we know that SSN 91783098 exists in our list of SSN.
Here is the formula for vlookup to be used in your excel spreadsheet.
=Vlookup(‘lookup value’,’table array’,’column index number’, ‘range lookup’)
Confusing?? Let’s understand each component separately.
Lookup Value = The Value that we want to find in the list. In our case, we are looking to find SSN 91783098
Table Array = This is simply our list of SSN where we are trying to find our data (SSN:91783098)
Column Index Number = Ignore this for now. We will learn about this is a short while.
Range Lookup = Match Type. (Exact Match = FALSE or 0 or approximate match = TRUE or 1)
Hence, now we can write above formula as per our requirement and it becomes:
=VLOOKUP(917823098,A2:A9,1,FALSE)
Look at vlookup fig below:
If no match is found that formula will return a value as #N/A
Excellent!! Whatever we did in above example was the simplest example of vlookup where we tried to find a single data in a column containing multiple data. Now we will focus to figure out how to find multiple matching data in a bunch of data. Let us consider the same example as above, however this time we have another set of data in column D. Have a look at the figure below.
As we see in this figure, we have added another group of data (Group B). Now we will try to look each data in Group A and will match with every data of Group B. We will do it as an exact match and any data in Group A not found in Group B will give output as #N/A. Let us start writing our vlookup formula in column B. Refer to figure below for clarification.
Great! We see that first SSN in Group A is not present in Group B. Let us drag our formula in B2 further down in column B until B9 to see what the results are for other SSN. Please refer figure below.
Clearly, we see that we have 3 match from Group A to Group B. We can also apply vlookup from Group B to Group A in similar manner. Have a look on figure 6 to see how vlookup results appeared when we try to match from Group B to Group A.
We apply the formula into cell E2 and will drag the formula from E2 until E9. Please do not forget to make sure you freeze the values in B2:B9 before dragging the formula by putting $ sign before the cell numbers ($B$2:$B$9). See how the vlookup gives the result for this match.
Now that we understand basics of vlookup function and how vlookup can be applied between two columns to find exact match or approximate match, we will go a little further and include more data in Group B. Our objective here is to “understand Column Index Number” that we skipped previously. In Group B, along with social Security Number, we will add First name, Last Name and Monthly Income. Group B now looks like this.
Use of Column Index Number: Column index number is used point to another column corresponding to a matching column. For example, in Group B corresponding columns for first SSN (320982233) are First Name, Last name and Monthly Income with values as John, Carter, $3500 respectively. So using column Index Number we can apply a vlookup to match first column i.e. column D containing SSN. If it matches, then we can display value of any corresponding column.
Lets understand this with an example. What we want to do is write a vlookup formula in column B to find the last name for each SSN present in Group A. We will pick each SSN from Group A, match it with SSN in Group B and the will get result as Last name if there is a match.
Here is the formula to achieve the goal: =vlookup(A2,$D$2:$F$9,3,0)
Now understand details of formula:
Formula Name = Vlookup
A2 = The value we want to look for in Group B (Lookup Value)
$D$2:$F$9 = Table array (Selecting all data where we are applying our formula. Look closely to see that this time we are including more data in table array then previous example. This is being done because we want Last name as formula result and not just a simple match)
3 = Column Index Number (We are using 3 in our formula because we see that Last name is in the 3rd column of our Table Array)
0 = For exact Match
As usual, if we do not have any match, we will get #N/A as our result. If we have a Match, we will get Last Name in Column B.
We can have different column index applied on matching SSN’s and we will get the desired results. For example, below formula will print Monthly Income in column C.
=vlookup(A2,$D$2:$G$9,4,0)
See how we are changing the Table Array selection and column Index Number to get the result.
This is all about vlookup. The formula is very simple to use and reduces a lots of effort when picked wisely. Often ignored due to not having an understanding. Please let me know if you have any related question.
Headhonk 