Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Merging two lists and Extracting Contact Numbers with a Known First and Last Name using VLOOKUP formula in Excel
Problem Statement
Suppose we have two lists that are defined in two distinct worksheets. One worksheet contains the basic details of the employee like the Employee ID, Employee First Name, and Employee Last Name whereas the other worksheet contains only two columns named Employee First Name and Contact Number. By using the VLOOKUP formula, we intend to get the contact numbers of the employees and append them to the first list. VLOOKUP is one of the most powerful functions to search for a specific value in a row.
To combine two lists using the VLOOKUP formula
Step 1
Users need to write the following details of the employees in Sheet 1 as illustrated below −
Step 2
Consider a sample data for Sheet2 −
Step 3
Write the text "Contact Number" in the D1 cell and enter the formula =VLOOKUP(B2,Sheet2!A1:B11,2,FALSE) in the D2 cell and press "Enter" to obtain the contact number of the corresponding employees. As you can see, the contact number is retrieved through this Vlookup formula as shown below image −
Explanation
Here we have used this formula −
=VLOOKUP(B2,Sheet2!A1:B11,2,FALSE)
B2 − We must search the B2 value of Sheet1 in the D2 cell.
Sheet2!A1:B11 − The second argument represents the range A1:B11 of the Sheet 2.
2 − It denotes the range of the second column "Contact Number" residing in the second sheet.
FALSE − The fourth argument "False" denotes the exact match which means VLOOKUP would return an exact match not an approximate match.
Step 4
Drag the "+" sign and drop till D11 to get the contact number of the remaining cells as highlighted in the below image −
Therefore, the contact number of employees is obtained in the D column.
Conclusion
In this article, we have merged two lists by applying the VLOOKUP formula and retrieved the contact numbers of certain employees. Make sure that there is a common column, "Employee First Name" in both tables. It is the easiest and quickest method to search for certain data in large datasets.
99 Views
