In Excel, many times we need to extract few or huge amount of data or entries from an excel sheet. For example, you have an excel sheet that contains a list of 102 person’s name, say the sheet name is “namesheet” and you want the phone number of each person from the existing excel sheet(dump) that contains 1050 person’s name, say on column “A” and their corresponding cell phone number on the another column,say on column “E”, say the sheet name is “numbersheet”.Lets look out how to apply VLookup formula.
Apply the VLookup formula, this is very useful for large and random data search.
The syntax for the VLookup function is:
VLookup( lookup_value,table_array,col_ index_num,[range_lookup] )
Step 1:Open both the sheets, first come to “namesheet”, let you have all the names on the column “A” and you want corresponding phone numbers on the column “B” then select a cell say cell “B1” just write =vlookup( then excel application will start to show you the syntax of Vlookup formula.
Step 2: Select whole column “A” this will be your lookup_value then press comma without doing anything just open excel numbersheet window. The vlookup syntax will follow you and it will not disappear.
Step 3: Select the whole column where the names are present and start dragging the cursor up to the phone number column while dragging on the top of the column number appears and it starts increasing as you drag the cursor on the right side towards the phone number column as the cursor selects the phone number column note the number leave dragging.
Step 4: Come to first excel sheet i.e. namesheet, the Vlookup syntax will follow you and it will not disappear, put comma and write the number you have noted down in the step 3 and again put comma.
Step 5: Write numeric zero ‘0’ and close small brackets that you have opened initially and press enter. It will come up with a phone number.
Step 6: Double click on the lower right corner of the cell containing the phone number, it will fill out the below cells with the corresponding phone numbers. They are the required phone numbers.
Step 7: One and an important step is remaining, select the whole column of phone numbers that u have just fetched ,copy it and then right click the mouse and select paste special option from it and from paste options select values option. Now, your data if free from formula and links. Save the excel sheet and use the phone number sheet as required.
Note:-
- Vlookup is only applicable when the dump excel sheet contains the phone number column on the right of name column.
- Don’t close any one of sheets while applying Vlookup formula.