Using VLOOKUP to find an exact match

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.

To begin downloading the practice workbook, click the following button:

For detailed instructions on downloading and extracting files in Google Chrome or Firefox, visit Getting Started with Exercise Files.

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,

Click

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

The exercise file opens to a worksheet called Bike Parts. There are two sections of data on this worksheet. In the range A7:D16, you have data that shows part numbers, supplier numbers, item descriptions, and prices for ten bicycle accessories. (Note that this data is in no particular order.) At the top of the worksheet, in the range A1:B4, you can see labels and cells reserved for part numbers, prices, supplier numbers, and supplier names.

We want to create a way to enter the part number in cell B1 and have the price of the related accessory automatically returned in cell B2. For example, when you type in "AB92" in cell B1, then "$25.16" will appear in cell B2.

When working with the VLOOKUP function, remember to place the function in the cell where you want the result to appear. In this example, that will be cell B2. Let's do this now.

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:
=vlookup
To enter the first argument, type:
(b1,

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:
a7:d16,

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:
4,

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:
false)

The function should look like this: =VLOOKUP(b1,a7:d16,4,false).

To enter the function, on the keyboard, press:
Enter key

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.

Using VLOOKUP to find an exact match

Acquiring the practice workbook

Downloading the practice workbook for Microsoft Excel

In Microsoft Excel, download and extract the practice workbook.

Copying the practice workbook in Google Sheets

In Google Sheets, copy the practice workbook.

Entering the VLOOKUP function

Step 1. Make cell B2 the active cell.

Step 2. Begin entering the function and the first argument.

Step 3. Add the range of cells to the function.

Step 4. Add the column number to the function.

Step 5. Specify an exact match.

Step 6. Enter the function.

Step 7. Enter the part number AB192 in cell B1.

Step 8. Enter the part number AB222 in cell B1.

Formatting a cell in Microsoft Excel

Step 1. If working in Microsoft Excel, format cell B2 as currency.