Last week, I introduced you to VLOOKUP, INDEX and MATCH. In this post, we’ll use VLOOKUP to find a value from the table.
Firstly, let’s create the above table in Excel. So whip up the installation of Excel you have. You can use either of Excel 2003, Excel 2007 or Excel 2010. I’ll be using Excel 2010 in my example. Download the examples file before you proceed. The file contains a Questions sheet which you can practice in and a Solutions sheet as well.
If you haven’t had a chance to review the basic syntax of the functions, please do read this post before you continue.
VLOOKUP is the easier function of the two to use and understand. It takes just 4 parameters and we’ll be using all 4. And, we’ll look at 4 options of using VLOOKUP.
Step 1: Basic usage by hard-coding lookup_value and col_index_num
In this stage we’ll use VLOOKUP in a very basic form. All parameters will be hardcoded. Our aim is to retrieve the Department and Full Name of Employee ID 38, i.e. Operations and Axel Delgado respectively.
The screenshot below gives you the formula that you will need to punch in. If you haven’t yet, download the Example file and refer to the “Question” sheet.
The formula tells excel to look up 38 in the first column of the table ($B$5:$D$13) and pull out from the value in the 2nd column in the row that is matched exactly (FALSE) for 38.
The result of this formula is Operations.
Similarly, using the formula in the screenshot below, you can look up the Full Name, i.e. Axel Delgado
Step 2: Cell-referenced lookup_value and hardcoded col_index_num
The power of VLOOKUP is that you don’t need to hardcode any of the parameters. The most common usage is to use a cell reference for the lookup_value. This ensures that you can change, in our example, the Employee ID and it will give you the Department and Full Name corresponding to that ID.
In the formula in the screenshot below, we have linked the lookup_value parameter to $B27. We use $ because we want to lock the column which allows to easily copy the formula left or right. However, remember to change the 3 to 2 when you copy it left.
B27 contains the number 38, which was what the first parameter in Step 1 above.
Go ahead and change the value of 38 to 41. You’ll notice the Department and Full Name change to Sales and Andreas Hauser respectively.
Step 3: Cell-referenced lookup_value and col_index_num
As I mentioned earlier, you can also cell reference other parameters of VLOOKUP. I usually like to reference the col_index_num as well since it makes it much easier to copy the formula left or right.
In this example, it is good practice to lock the row of D31, so that you can copy down. Check Step 5 of the Excel file.
Step 4: Cell-referenced lookup_value and matched col_index_num
Let’s make this a bit more complicated. In Step 3, we had hard-coded the column numbers (i.e. row 31). In case you have several columns which may not be in the same order as the original table, then you can consider using MATCH to give you the column number.
Refering to last week’s post, MATCH requires 3 parameters as you can see in the screenshot below.
The formula above tells excel to match exactly (0) the value Full Name in the range ($B$4:$D$4). This will return the value 3 which is the 3rd column of our VLOOKUP example. The remaining formulae stay the same as Step 4.
I hope the examples above gives you an insight on VLOOKUP. It is one of my most used functions, because of its ease of use and the power that it gives you. VLOOKUP does have a few limitations, the main one being that the data that you want to match has to be present in the first column of the array. In our example above, this is the Employee ID.
A combination of INDEX and MATCH helps you overcome this. We’ll take a look at this in next week’s post.
If you have any questions regarding VLOOKUP, leave a comment below and I’ll try to answer it. You can also try Stack Overflow which gives you access to a much larger community of users.