Using VLOOKUP between two worksheets is very similar to using VLOOKUP on the same worksheet.
We are going to use two sheets (Sheet1 and Sheet2). You can find this example, under this link.
Sheet1
On Sheet1 we have a list of the first and last name, the unique phone number, and the city that the person comes from.
Sheet2
On sheet2 there is also a phone number, by which we would like to identify the person and an email which we would like to add to the first table.
Both data are inside the same workbook but in different worksheets.
Use VLOOKUP to get data from another worksheet
- Copy both examples to Excel Sheet1 and Sheet2, inside the same workbook.
- On Sheet1, create a column called Email.
- In cell E2 enter the following formula:
|
1 |
=VLOOKUP(C2,Sheet2!$B$2:$C$51,2,FALSE) |
- Autofill the rest of the rows inside the column.
Formula explanation
The VLOOKUP function takes cell C2 as the unique identifier for the first person.
Next, you have to specify the table array. The first column should be an identifier. That’s why we used Sheet2!$B$2:$C$51, instead of Sheet2!$A$2:$C$51.
Next, we choose the second column in our selection, which is column C (Email). At the end we use FALSE, meaning that has to be the exact match.