Learndreamskill

Hi Ankita, VLOOKUP is used to search the values vertically. VLOOKUP function takes 4 parameters 1. Lookup value 2. Table array 3. col_index_num, [range_lookup] Suppose we have below two datasets Dataset A Name Vikas Ajay Ravi Ankita Pankaj Dataset B Name Age Vikas 28 Ajay 32 Ravi 60 Ankita 24 Pankaj 56 based on above both data, we have requirement to fetch the age from Data B to the Data A based on Names field. Therefore we need to use below steps to apply vlookup 1. Suppose data A is name values are starting from cell A2 and we need to fetch the names from data A so we need to apply VLOOKUP with data A. so we will apply vlookup to b2 (we can apply formula to any cell but B2 cell will make data more readable) cell adjacent to a2 cell. 2. Now We will type =Vlookup( into B2 cell. after that we need to give the lookup value to vlookup which is cell a2 in our case as we want to fetch the names for a2 and all below cell. so vlookup will be having A2 as first parameter and will look like =vlookup(a2 then we need to apply comma(,) to start adding second parameter to vlookup function with comma vlookup will look like =vlookup(a2, 3. Now we need to add the table_array parameter to vlookup which is the data from where we want to fetch the values therefore in our case we have data B which we will there in suppose sheet 2. after adding comma(,) after first parameter, we need to go to the sheet 2 and select data B. second parameter data selection can be done using two ways 1. either go to sheet 2 where we have data B, and select data with your mouse which will add second parameter like Sheet2!A1:B10 where range is just for explaining you. But you will notice here range is relative here so we need to make range absolute as well by either press F4 function key just after selecting data with mouse which will make table_array parameter look like Sheet1!$A$1:$B$10. absolute cell reference help vlookup to work properly. 2. second way to give table array parameter is just select the columns of data b from sheet 2 instead of selecting complete data which will look like Sheet2!A:B. In this case we do not need to make cell reference absolute as well. after selecting second parameter please add comma(,) also to start adding the 3rd parameter. At the end of 3 point our vlookup will look like =vlookup(a2,Sheet2!A:B, or =vlookup(a2,Sheet2!$A$1:$B$10, 4. we need to give col_index_num as 3rd parameter to vlookup which will be the value we want to return. like in our data B we want to fetch the age and age is there in second column as in first column, we have name. Col_index_number depends on the table_array data selection as well so we have selected column A (contains name) column B (contains age) therefore column A becomes col_index 1 and column B becomes col_index 2. Now we need to give the 2 in our vlookup function as col_index_num as we want to fetch the age. suppose if we have three columns name address age then we will give the col_index_num as 3. POINT TO NOTE in the table_Array data lookup_value column first present before the column of value which we want to extract like we want to extract age from data B based on the names of data A therefore column sequence in data B must be Name Age not the Age Name. If you do not have data aligned like this simple vlookup will not work here we will have to write advance version of vlookup which you can learn from www.learndreamskill.com. After adding col_index_num please add comma(,) to start adding the 4th and last parameter. So at the end of point 4, our vlookup will look like =vlookup(a2,Sheet2!A:B,2, 5. Now we need to add the last parameter [range_lookup]. You will see that range_lookup parameter is enclosed in round brackets which means it is the optional parameter. this is true for all excel functions. [range_lookup] take two option 0 or 1 as value where 0 means extract match type and 1 means approx match type. we need to give 0 here because name will be matched extract 1 works with numeric values. now please close the parenthesis. now our formula will look like =vlookup(a2,Sheet2!A:B,2,0). we are done with writing our formula. you need to press enter and you will see the age for the name in cell a2. now please drag your formula below for all names in data A. You can learn more advance version of lookup at www.learndreamskill.com we have created too good tutorials based on live scenarios requirements. Thanks

Hi Rahul, Below are steps to create pivot table. 1. First select data which you want to use to create pivot table. 2. (Select Insert Ribbon --> Tables Section --> Pivot Table button --> Choose Pivot table) or press Alt -> N -> V -> T. 3. By performing action from point 2, you will get Create Pivot Table small window or popup visible where you see 2 section. in 1st section, you will find data is already selected and in 2nd section, it will ask you to add pivot table location to either new worksheet (Default selection) or existing worksheet. Press OK once you are done 4. You will find new sheet added where you will see Pivot table area in sheet and Pivot Table Field List pane to select the fields. 5. Pivot Table Field list pane contains 5 section (1. List of fields in raw data 2. Report Filter - we put fields here which we want to see as filter in pivot table 3. Column Labels - We add those field in this section which we want to see in column headers 4. Raw label - We add those field here which we want to see in raw label 5. Values we add those field here which we want to use for statistics like count, sum, min, max) 6. What ever fields you will add as per point 5, will be reflected as pivot table in sheet pivot table area. 7. To learn more enjoy excel tutorial on www. learndreamskill.com