VLOOKUP Explained

Have you ever had the need to combine two sets data on a spreadsheet? Ever needed to compare two spreadsheets that have some matching data like serial or check numbers? If you answered yes to either question you need to use a VLOOKUP function in Excel. This is one of my favorite and most useful formulas. If you are not familiar with VLOOKUP I promise this article will save you more time than you spend manually figuring these questions out in Excel.

Let's start with a break down of how to set up the formula. When you start typing VLOOKUP in Excel you will notice a small shadow box appear below that says "VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])" Because you are fluent in Excel speak you completely understood that right? Let's break down each part to help us better interrupt what Excel is asking for:

• lookup_value = What you are looking up (i.e. Serial Number)
• table_array = Where you are searching for the lookup_value and result (What)
• col_index = Return this value by a count of the number of columns from the lookup_value (What)
• [range_lookup] = Exact or approximate match to the lookup_value (What)

So re-worded that grey shadow box makes this a little easier to digest VLOOKUP(What, Where, Return this, Exact or Approx Match). Now that we understand what Excel needs we can start to put this to practical use. Here is an everyday example of VLOOKUP:

You need to merge two sets of inventory data. One set of data contains the Model, Serial, and Description. The second set contains the Serial and Cost. You are trying to combine the data so that 1 set of data has the Model, Serial, Description, and Cost. A VLOOKUP formula is exactly what you need to combine these two data sets. Below is a screen shot of the data and VLOOKUP formula.

Let's review my formula above. VLOOKUP(B4,F:G,2,FALSE)

• What: B4 = The Serial number in Set 1 that I would like to look up/cross reference in Set 2.
• Where: F:G = The place where I want Excel to look for the Serial Number and also where the information I want returned is. Noticed I used the entire column instead of the exact data set F3:G10. This method makes copying the formula easier as you will not need to make these cells/columns absolute ($F$3:$G$10) since the formula looks in the entire column.
• Return this: 2 = Since the information I want to return is the cost I need to get that from the second column in data set 2. By count that equals 2. If there were another field in data set two between Serial and Cost, let's say:| Serial | Warehouse | Cost |. Then I would use 3 instead since the value I would like to return is the 3rd column from the Serial number or the What part of my equation.
• Exact or approximate match: FALSE = The value we are looking up from Data Set 1 should exactly match the value in Data Set 2 therefore we would use FALSE to find an exact match.

After entering the formula we can now copy it down column D to fill in the costs for all of my items in Data Set 1. Below is the final result:

VLOOKUP is an extremely powerful tool that can help you compare and combine data for several everyday tasks like: Bank Reconciliations, Vendor Statements, Inventory Reports, and Revenue Forecasts. I hope you found this article helpful and if you have any question about VLOOKUP feel free to message me.