How to use vlookup in excel?

vlookup means vertical lookup. VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Lookup_value The value to search in the first column of the table array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.). Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value. Table_array Two or more columns of data. Use a reference to a range or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent. Col_index_num The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is: Less than 1, VLOOKUP returns the #VALUE! error value. Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value. Range_lookup A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. For more information, see Sort data. If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned. Remarks When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not have leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP may give an incorrect or unexpected value. For more information, see CLEAN and TRIM. When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, VLOOKUP may give an incorrect or unexpected value. For more information, see Convert numbers stored as text to numbers. If range_lookup is FALSE and lookup_value is text, then you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character. read less

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 read less