Our first goal is to use the VLOOKUP function to search for and find data contained in a single worksheet.
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 in a new browser window,
Right-click Copy practice workbook, Click Open link in new window
The file begins to open in a new window of the web browser. Before the document will open and can be edited, we need to make a copy of it.
Move to the new browser window.
- To copy the workbook file, in the browser window,
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 the VLOOKUP function
- To make cell B2 the active cell,
Click cell B2
The first argument added to the VLOOKUP function is the lookup_value (Microsoft Excel) or search_key (Google Sheets). This is the argument that tells the spreadsheet what to look for. In this example, we want a user to enter a part number in cell B1 so our function will look for the value in that cell.
Arguments in the VLOOKUP function are contained within parentheses and are separated by commas.
- To begin entering the function, type:
- To enter the first argument, type:
The second argument added to the function is table_array (Microsoft Excel) or range (Google Sheets). This argument tells the spreadsheet where to look for the value in cell B1. In this case, the range of cells that contains the parts list, A7:D16.
To add the range of cells to the function, type:
The third argument is col_index_num (Microsoft Excel) or index (Google Sheets). This argument tells the spreadsheet which column contains the data you want to be returned. In this example, we want the price to be returned. The prices are in the 4th column of the range. Therefore, we will add "4" to our function.
- To add the column number, type:
The final argument is range_lookup (Microsoft Excel) or [is_sorted] (Google Sheets). This argument tells the spreadsheet if you want an exact match (false) or an approximate match (true). We are looking for an exact part number, so we will use the false argument.
- To specify an exact match, type:
The function should look like this: =VLOOKUP(b1,a7:d16,4,false).
- To enter the function, on the keyboard, press:
We get an unexpected result, a #N/A error. This error occurs when the spreadsheet program can't find a reference needed to complete the function. In this case, the VLOOKUP function is looking for data in cell B1, however, cell B1 is currently empty. Before the VLOOKUP function can work correctly, we need to put a part number in cell B1. We'll look up the price for part AB192.
- To put a part number in cell B1, type:
AB192 Enter key
The price of part AB192, $22.95, appears in cell B2. Let's see the price of part AB222.
- To enter the new part number,
Click in cell B1, type: AB222 Enter key
The price in cell B2 adjusts for each part number entered.
Formatting a cell in Microsoft Excel
If you are working in Microsoft Excel, you may have noticed that the price in cell B2 is not formatted as currency. This can be easily remedied.
NOTE: If you are working in Google Sheets, you can skip the following step.
- To make cell B2 the active cell,
Click in cell B2
- To format cell B2 as currency, on the Home tab,
Click , Click Currency
Let's move along and add a second VLOOKUP function to this worksheet.