UrbanPro

Learn Microsoft Excel Training from the Best Tutors

  • Affordable fees
  • 1-1 or Group class
  • Flexible Timings
  • Verified Tutors

How to use vlookup in excel?

Asked by Last Modified  

Follow 1
Answer

Please enter your answer

Accounts professional trainer with more than 10 years if experience in teaching & corporate finance

Syntax for using vlookup function is =VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUMBER)
Comments

find out the value. one sheet to another sheet
Comments

Trainer

vlookup formula searches data vertically. Make sure your lookup value should be in first column of lookup array, freeze the array if need to drag. Give the column index and false for exact match the value in array
Comments

C/C++/SQL/Datastructures/Database/Excel

I can help you with it, connect with me
Comments

Oracle, Hindi, C++, C, MS Office, VBScript,JavaScript,Spoken English etc with 29 years of experience

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...
read more
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
Comments

Vloookup is requires 4 parameter values as input. 1. Lookup value 2. Lookup Table range 3. Index No. of the required value 4. Whether it should be exact or approximate match.
Comments

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, 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...
read more
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
Comments

View 5 more Answers

Related Questions

How many days it will take to complete advance excel course?

The duration to complete any kind, of course, depends on your grasping power and the ability to learn the concepts quickly. Having said that, it usually takes about three to four weeks to learn the topics...
Amit
What does the VLOOKUP in MS Excel function do?
Basically, VLOOKUP lets you search for specific information in your spreadsheet. For example, if you have a list of products with prices, you could search for the price of a specific item.
Nayana
1 0
5

Is learning Tally good or is learning Advanced Excel and Python good? Which one will be better for getting a job? What would be the general cost of learning Tally, Advanced Excel and Python? Which are the best tutors or institutes near to Kudlu Gate and Electronic City?

 

 

As a beginner, you should go with Advance Excel and MIS reporting. After that, you can go for VBA, SQL, and Python in the end. Follow this career path. You will end up being a data analyst and package raging from 8-12lakh pa.
Prabhu

Now ask question in any of the 1000+ Categories, and get Answers from Tutors and Trainers on UrbanPro.com

Ask a Question

Related Lessons

Learning Microsoft Excel
All Excel courses that are currently being offered online as well as classroom sessions have a lot in common – In fact they are all the same thing. Beginner, Intermediate, Advanced, VBA etc. The...

How to Remove Duplicates from your data
Larger data sets tend to have duplicate content. You may have a list of multiple contacts in a organization and only want to see the number of organization you have or you have multiple rows of same type...

Hide Duplicate Values & Apply Borders Using Conditional Formatting
Please refer following screenshots:1st Screenshot: Original Dataset2nd Screenshot: After Conditioinal Formatting is applied In 2nd screenshot, using Conditional Formatting:1) Repeat months are...


Hidden Gems Of MS Excel - Sort (add Multiple Levels to Sort Data)
Please refer following dataset: You would know:- To Sort Month from Oldest → Newest OR Newest → OldestOR- To Sort Region from A → Z OR Z → AOR- To Sort Units Sold from Smallest →...

Recommended Articles

Microsoft Office is a very popular tool amongst students and C-Suite. Today, approximately 1.2 billion people across 140 countries use the office programme. It is used at home, schools and offices on a daily basis for organizing, handling and presenting data and information. Microsoft Office Suite offers programs that can...

Read full article >

Microsoft Excel is an electronic spreadsheet tool which is commonly used for financial and statistical data processing. It has been developed by Microsoft and forms a major component of the widely used Microsoft Office. From individual users to the top IT companies, Excel is used worldwide. Excel is one of the most important...

Read full article >

Applications engineering is a hot trend in the current IT market.  An applications engineer is responsible for designing and application of technology products relating to various aspects of computing. To accomplish this, he/she has to work collaboratively with the company’s manufacturing, marketing, sales, and customer...

Read full article >

Whether it was the Internet Era of 90s or the Big Data Era of today, Information Technology (IT) has given birth to several lucrative career options for many. Though there will not be a “significant" increase in demand for IT professionals in 2014 as compared to 2013, a “steady” demand for IT professionals is rest assured...

Read full article >

Looking for Microsoft Excel Training classes?

Learn from the Best Tutors on UrbanPro

Are you a Tutor or Training Institute?

Join UrbanPro Today to find students near you
X

Looking for Microsoft Excel Training Classes?

The best tutors for Microsoft Excel Training Classes are on UrbanPro

  • Select the best Tutor
  • Book & Attend a Free Demo
  • Pay and start Learning

Learn Microsoft Excel Training with the Best Tutors

The best Tutors for Microsoft Excel Training Classes are on UrbanPro

This website uses cookies

We use cookies to improve user experience. Choose what cookies you allow us to use. You can read more about our Cookie Policy in our Privacy Policy

Accept All
Decline All

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more