My Version of a VLookUp Tutorial

So today I was given 2 lists and I needed to find out what they had in common and what there differences were. The basic gist of VLOOKUP is that it will take a value and look through a huge table to see if it closely or exactly matches any values in the table.

So how do you do it?

Let's take a simple example; we are going to see how many of our favorite foods are actually fruits. So in column 1 we will have a list of fruits and in column 2 we will have a list of our favorite foods. 

We need to know right the function to take the value in column B and search column A to see if it exists. Remember this is a simple example just to get the concept down, I promise this comes in handy when you have a huge dataset. When you pull up the help center article in Excel for the function this is what they give you "VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)" A bit daunting in my opinion!

Breaking it down we know that VLOOKUP is the name of the function. 

• 'Lookup_valule'  is what our first parameter is going to be that we want to take and see if it exists in our list. In the first row it would be 'pizza.'
  
• 'table_array' this is the master list you are trying to compare against. In our case it is fruits.
• 'col_index_num' says if I find the value that I'm looking for, what column should I return data from. This number is much more useful if you are searching a table that has multiple columns. For example, names of people and their age, we might be looking for their name, but want to return column 2, their age. In our case we are just going to put 1, which states if you find a match, return the fruits name
• 'range_lookup' this can be true or false. This is a bit convoluted, but if you put 'TRUE' it will look for partial matches. If you put 'FALSE' it will require an exact match. In our case we want to put 'FALSE'

Based on all of that the formula that I would put into column C row 2 would be =VLOOKUP(B2, $A$2:$A$7, 1, FALSE). The result you should see is '#NA' which means that it could not find 'pizza' in the list of fruits. Next step is to drag or copy the formula down to column 6 so it is next to each of your favorite fruits. It should look something like this.

The only other thing that you may not be familiar with is the '$' that I used in the formula. This is a way to anchor your formula so when you drag it down it doesn't automatically adjust the values. We didn't use it for B2 because we wanted it to change to C2, D2, etc. However, we wanted the range of fruits to stay the same.

Once you have the data I usually copy and do a paste special to choose just the values and manipulate however I need to. This is by no means the fastest or most eloquent way to do this type of thing, but I thought I would share it since I spent a good deal of time working on this today.