There is one remaining piece of information we need, supplier name. As with the price and supplier number, we can use the VLOOKUP function to get this information. The supplier names are not on the Bike Parts worksheet. They're on a separate worksheet called Supplier-Names. The VLOOKUP function can be written to use references on a different worksheet, or even workbook.
Acquiring the practice workbook
If you are beginning this course at this point, you will need to access the practice workbook.
Downloading the practice workbook for Microsoft Excel
If working in Microsoft Excel, you will download and extract the workbook.
Copying the practice workbook in Google Sheets
In Google Sheets, you will copy the workbook to your Google Drive.
- To begin copying the practice workbook, click the following button:
The file begins to open in a web browser. Before the document will open and can be edited, we need to make a copy of it.
- To copy the workbook file,
A copy of the file is now open and, by default, is saved in your Google Drive. The filename now has the words "Copy of" at the beginning.
Entering a VLOOKUP function across worksheets
The Supplier-Names worksheet contains the supplier numbers and corresponding supplier names. The supplier numbers are the same as the supplier numbers on the Bike Parts worksheet. We can create a third VLOOKUP function that uses these supplier numbers to return the supplier names.
- To move to the Supplier-Names worksheet, at the bottom of the spreadsheet,
Click the Supplier-Names tab
Let's return to the Bike Parts worksheet and enter the function.
When complete, the function will look like this:
This function is saying, "Look for the value in cell b3 in the Supplier-Names worksheet, range a2:b9, return what is in the second column of that range, this should be an exact match."
- To move to the Bike Parts worksheet, at the bottom of the spreadsheet,
Click the Bike Parts tab
We want the supplier name to appear in cell B4 so that is where we will put the VLOOKUP function.
In the previous section, we used a VLOOKUP function to find the supplier number and place it in cell B3. We will now write another VLOOKUP function to search for that result.
- To make cell B4 the active cell,
Click in cell B4
- To begin entering the VLOOKUP function, type:
The next argument in the VLOOKUP function asks where we want to look for the supplier number. We want to look in the Supplier-Names worksheet. With cell B4 still active and the VLOOKUP function incomplete, we can move to the Supplier-Names worksheet and choose the correct range of cells or we can type, 'Supplier-Names'! a2:b9. Let's move to the Supplier-Names worksheet.
- To move to the Supplier-Names worksheet, at the bottom of the workbook,
Click the Supplier-Names tab
- To select the correct range of cells,
Press & drag from A2 to B9
It may appear as if you are overwriting data on the Supplier-Names worksheet. This will be remedied when the VLOOKUP function is complete and entered.
We will continue to work in the Supplier-Names worksheet. It is not necessary to move back to the Bike Parts worksheet at this time.
- To tell the function which column to return, type:
- To tell the function to find an exact match, type:
false) Enter key
You are returned to the Bike Parts worksheet. A supplier name is now in cell B4. Depending on the part number in cell B1, this supplier name will vary.
Our worksheet now has three VLOOKUP functions working together to give up the price, supplier number, and supplier name all based on the part number entered in cell B1. Let's see how it works.
- To enter a new part number in cell B1,
Click in cell B1, type: AB92 Enter key
The information about part AB92 appears:
Now let's see how to use the VLOOKUP function to find an approximate match.