Using VLOOKUP to find an approximate match

In the previous sections, we saw how to use the false argument in the VLOOKUP function to return an exact match. There may be times when an approximate, or closest, match is desired. For example, a grading scale or a range of dates. In these spreadsheets, we want the VLOOKUP function to find the closest value without going over. When looking for an approximate match, the range where you are searching must be organized in ascending order.

In the following example, we are looking for a student's final grade. Grade assignments typically span several points. A student's total points may not exactly match the points listed. By using the true argument within the VLOOKUP function, we can find the closest total points.

=VLOOKUP(B2,$F$6:$G$19,2,true).

This function is saying, "Look for the value in cell B2 in the first column of the range F6:G19. When a value higher than the value of cell B2 is reached, stop and go back to the previous value, and return the final grade in the 2nd column." The following image shows this in more detail.

Image of a spreadsheet. Details are explained in the text.

NOTE: This VLOOKUP example uses absolute cell references. These references are discussed in the section titled Using absolute cell references in the VLOOKUP function.

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:

Exercise Files

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

This Bulk Discounts worksheet was created to keep track of sales. Depending on how many items a customer orders, there is a discount. The more items ordered, the larger the discount. We will use the VLOOKUP function to calculate the discount for each order.

Before we begin entering the VLOOKUP function, we need to navigate to the correct worksheet.

To navigate to the Bulk Discounts worksheet, at the bottom of the workbook,
Click the Bulk Discounts tab

In this example, if a customer orders 1-24 items, they do not receive a discount. But if 25-99 items are ordered, it is a 2% discount. Anything between 50,000 and 99,999 is a 16% discount and 100,000 or more is a 20% discount. These discounts are in ascending order in cells J3:K11.

The base price of the item is $14.99.

Let's use the VLOOKUP function to learn what discount each order will receive. Then we will use formulas and functions to fill in the rest of the worksheet. We will begin by making cell D3, the cell where we want the result of the VLOOKUP function, the active cell.

To make cell D3 the active cell,
Click in cell D3

Take a moment to think through what we want the VLOOKUP function to do. We want to look for the value in cell B3 in the range J3:K11 and return the closest discount without going over.

The function will look like this: =VLOOKUP(B3,J3:K11,2,true).

To enter the VLOOKUP function, in cell D3, type:
=vlookup(B3,J3:K11,2,true) Enter key

The result appears in cell D3. Depending on the program you are using, Microsoft Excel or Google Sheets, you will see either .08 or 8%. Looking at the range J3:K11, it is easy to see that 8% is the discount when ordering 1000 units.

We need to find the discount amounts for all of the orders, not just the first one. Let's copy the VLOOKUP function to the other rows.

To make cell D3 the active cell,
Click in cell D3
To copy the VLOOKUP function, on the keyboard, press:
Control key + C
To select the range D4:D9,
Press & drag D4 to D9
To paste the VLOOKUP function to the range D4:D9, on the keyboard, press:
Control key + V

The results aren't what we expected. The first three rows seem to have worked correctly, but beginning at cell D6, we have #N/A errors. This error indicates that some part of the function is not available therefore the function can not be completed.

To see how to fix this error, proceed to the next section of this course, Using absolute cell references in the VLOOKUP function.

Using VLOOKUP to find an approximate 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. Navigate to the Bulk Discounts worksheet.

Step 2. Make cell D3 the active cell.

Step 3. Enter the VLOOKUP function.

Step 4. Copy and paste the VLOOKUP function in column D.