The vlookup function is an inbuilt function that does the vertical lookup by searching a value in the columns and returning the value in the rows. This function is used to look up and extract the particular data from the column. Let’s take a look on how to use the Vlookup function in Excel 2010 step by step process.
VLookup Function in Excel
Step 1 – Create/copy the data from the excel below. You can download the excel by clicking this link vlookup function in excel
The Excel has two worksheets – first named ‘Marks’ and other marked ‘Age and Gender’. The first worksheet contains the students name, their class, and Marks. The task is to populate the age and gender for respective students using the data in worksheet ‘Age and Gender’. This second worksheet contains the student’s name and their respective age and gender. Here we are going to apply vlookup between sheets.
Step 2 – Let’s try populating Vikas’ age in column E3. Type the following in column E3 =vlookup( You will see excel showing you the hint below. This contains the Vlookup function. It says VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
Step 3 – Let’s go through these four important parameters of Vlook function [=VLOOKUP(B3,’Age and Gender’!$B$3:$D$8,2,0)] in detail before you type it.
- Lookup_value is the value that you want to compare between two data sets (in our case, worksheet 1 and worksheet 2). Thus it has to be name of student. You want to lookup student name in these two excel to get Age and Gender.
- Table_array is the set of data in which you want to find matching records for your lookup_value. Thus, in our case, it will be cell no B3 to D8 in the worksheet ‘Age and Gender’.
3. Col_index_num is the index of the column that you want to fill in case the match exists. Excel starts the index number from 1 for the selected data range in Table_array. Since we will select B3:D8 in table_array, column B is index#1, column C is index#2 and so on. In order to populate age – we will have to take data from column C in the ‘Age and Gender’ excel and thus, have to choose col_index_num as 2.
4. Range_lookup needs value as true or false. False means Vlookup Exact Match whereas true means vlookup approximate match. This can also be denoted by typing integer 0 in this field.
In 99% of my real world excel usage, you will end up using exact match i.e. False or 0.
Now that you have understood what needs to go in these parameters and why – Let’s jump on to populate Student’s age in the first worksheet.
Step 3 – In cell#E3, type =VLOOKUP(B3,’Age and Gender’!$B$3:$D$8,2,0) and press enter. You will see the value ‘19’ in cell E3.
Step4 – Click on cell E3 (or use the arrow keys to bring the excel focus back on E3). Do Ctrl+C (Copy)
Step5 – Select column E4 to E6 and do Ctrl+V (paste)
Now let’s revisit the formula – the first parameter was student name, B3. The second was ‘Age and Gender’!$B$3:$D$8- this means that my data set from which value needs to be picked up is present in the worksheet named ‘Age and Gender’ and data range is from B3 to D8.
What is Dollar sign (absolute reference) in Excel?
Now you must be thinking what these dollar signs are doing in between B3 and D8!! By putting dollars in B3 and D8, we have ensured that when we copy-paste the formula, the excel doesn’t change these references. These are called absolute references and can be obtained by pressing F4. (more on that later).
Step 6 – Let’s try this formula again but this time, let’s do it the smart way – rather than typing the entire formula, let’s use Excel features and simply select the data rather than typing the references. So, delete the values in column E3 to E6 in the first worksheet.
Step 7 – Type the vlookup formula “=vlookup (For the first parameter, click on cell B3. Type , (comma). For the second parameter, click on the second worksheet (‘Age and Gender’) and select data range from B3 and D8. Type , (comma). Type 2,0) and press enter. If you have done it right, you see 19 getting populated in cell E3 of the first worksheet in ‘Age’ column.
Step 8 – Click on cell E3. You will see the vlookup formula in excel formula bar. (See screenshot below to see where formula bar is). Move your cursor in b/w B and 3 in the formula and press F4.
You will see that excel has automatically appended $ sign before both B and 4. Do the same for D8 in the formula. Click b/w D and 8 (alternatively move your cursor b/w D & 8 and press F4). Press enter and you are almost done.
Step 9 – Copy formula from cell E3 and copy paste the same from E4 to E6. Now you will see the ages of all students mentioned in worksheet 1.
Now, that you have mastered Vlookup in excel – here is the quick test for you. Populate Gender for respective students in Column F (Gender) in the worksheet ‘Marks’ by using Vlookup and data from worksheet ‘Age and Gender’. In the next post, you can also see the guide for How to create excel pivot table and conditional formatting excel formula.
Do let us know if you have any question/remarks about this vlookup tutorial.