In Excel, we usually have to work with more than one worksheet. The VLOOKUP function is able to be used on another sheet which allows the ability to better organize our data and perform calculations on it. This tutorial will assist all levels of Excel users in performing a VLOOKUP from another sheet.
Figure 1. Final result: VLOOKUP from another sheet
Working formula: =VLOOKUP(B3,Sheet2!$B$2:$D$7,2,FALSE)
VLOOKUP in Excel
VLOOKUP is used when we want to look up and retrieve data from a given data set. To look up from another sheet, the syntax is:
Syntax
=VLOOKUP(lookup_value, sheet_range, col_index_num, [range_lookup])
Where
- Lookup_value: the value we want to search and find in another worksheet
- Sheet_range: the range of cells in another worksheet containing the data we want to retrieve
- Col_index_num: the column number in the sheet_range corresponding to the information we want to retrieve, relative to the first column
- [range_lookup]: optional; value is either TRUE or FALSE;
- if TRUE or omitted, VLOOKUP returns either an exact or approximate match; it is important to sort the first column of the table_array in ascending order to ensure that VLOOKUP returns the correct value
- if FALSE, VLOOKUP will only find an exact match.
Setting up the Data
Here we have an Excel file with 2 worksheets.
Sheet1 has a list of student numbers and corresponding grade levels, but with missing first and last names.
Figure 2. Sample data in Sheet1 with missing first and last names
In Sheet2, we have a list of student numbers and corresponding first and last names.
Figure 3. Sample data in Sheet2 with complete names per student number
VLOOKUP from Sheet2
We want to populate columns D (First Name) and E (Last Name) in Sheet1 with the data in columns C (First Name) and D (Last Name) from Sheet2.
Retrieving the First Name
In cell D3, enter the formula:
=VLOOKUP(B3,Sheet2!$B$2:$D$7,2,FALSE)
Where:
- B3 is the lookup value, which is the student number; it is the common value between the two sheets that we will use to retrieve the information from Sheet2 to Sheet1
- Sheet2!B2:D7 is the range of data in Sheet2 where we want to search for the student number, in order to retrieve the first names and last names
- The dollar symbols “$” in $B$2:$D$7 fix the data range to make it easier to copy and paste the formula to other cells later
- 2 as the col_num_index means that we want to retrieve the value in the second column of the data range B2:D7, which is the First Name (under column C)
The formula returns the first name Anna for student number 38005.
Figure 4. Entering the VLOOKUP formula for first name from another sheet
Retrieving the Last Name
In cell E3, enter the formula:
=VLOOKUP(B3,Sheet2!$B$2:$D$7,3,FALSE)
This is a similar formula, only this time, the col_num_index used is 3, which means that we want to retrieve the data in the third column of the data range B2:D7 in Sheet2, which is column D, containing the Last Name.
Figure 5. Entering the VLOOKUP formula for last name from another sheet
To complete the first names and last names, enter the formula as shown in the tables below:
Figure 6. VLOOKUP from another sheet for first names
Figure 7. VLOOKUP from another sheet for last names
Now we have successfully populated columns D and E in Sheet1, by using VLOOKUP to retrieve data from Sheet2.
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Are you still looking for help with the VLOOKUP function? View our comprehensive round-up of VLOOKUP function tutorials here.
Leave a Comment