In the previous section, a VLOOKUP function was placed in cell D3 and copied down column D. While the first three instances of the VLOOKUP function worked as expected, in cell D6, we began getting #N/A errors. The #N/A error indicates that some part of the function is not available therefore the function can not be completed. Let's see how to fix this problem.
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.
Examining the VLOOKUP function
We are working in the Bulk Discounts worksheet. Before we can fix this #N/A error, we need to know what caused it. Let's look at each cell in column D. We'll start with cell D3.
- To make cell D3 the active cell,
Click in cell D3
With D3 as the active cell, we can see the VLOOKUP function, =VLOOKUP(B3,J3:K11,2,true) in the Formula Bar. This may look slightly different, depending on whether you're using Microsoft Excel or Google Sheets.
Microsoft Excel Formula Bar: 
Google Sheets Formula Bar: 
Let's continue down column D and see how the function adjusts.
- To move to cell D4, on the keyboard, press:
Down Arrow key
In cell D4, the VLOOKUP function is as follows: =VLOOKUP(B4,J4:K12,2,true).
The rows used in the function have adjusted in relation to the active cell. We're in cell D4 so the function has changed to look for the value in B4 in the range J4:K12.
- To move to cell D5, on the keyboard, press:
Down Arrow key
Again the rows in the function have adjusted relative to the row containing the active cell, row 5.
- To move to cell D6, on the keyboard, press:
Down Arrow key
Cell D6 contains the first occurrence of the #N/A error indicating that there is an argument in the function that is not available. What changed between row 5 and row 6?
With D6 as the active cell, examine the Formula Bar. The VLOOKUP function now looks like this: =VLOOKUP(B6,J6:K14,2,true).
Once again, the function has adjusted based on the current row. But when we look at the range J6:K14, we can see that this range has moved so far down, it no longer contains all of the necessary data.
Our VLOOKUP function is looking for the value in cell D6, 98. There is no value in the range J6:K14 that is close to, but not over, 98. The smallest value is 250. Therefore, the function can not be completed.
The same problem occurs in the remaining rows. The values being looked for are not contained within the range. This problem can be fixed by using absolute references in the VLOOKUP function.
Adding absolute references to the VLOOKUP function
When working in a spreadsheet, by default, cell references are relative and adjust as you move down through the rows. However, when working with the VLOOKUP function, we want the lookup range to stay consistent. We can use absolute references to make this happen.
To make a reference absolute, place dollar signs ($) before the parts of the reference you want to remain the same. In this case, we want both the column and row to stay as they are so we will add dollar signs ($) before each part of the range.
Let's return to cell D3 and make a change to the original VLOOKUP function.
The new function will look like this: =VLOOKUP(B3,$J$3:$K$11,2,true).
- To return to cell D3, on the keyboard, press:
Up Arrow key several times until D3 is the active cell
- To open the function for editing,
Double-click cell D3
- To select the range J3:K11, in the Formula Bar,
Press & drag J3:K11
- To make the range absolute, on the keyboard, press:
F4 key
NOTE: Alternatively, you could type: $J$3:$K$11.
- To accept this change, on the keyboard, press:
Enter key
There is no visible change to cell D3. Let's copy the revised function to the other rows in column D.
- 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 correct discount percentages now appear in column D.
If you are working in Microsoft Excel, the percentages in column D may appear in the general format, not percentage format. The column can easily be formatted to look like percentages.
- To select the correct cells,
Click in cell D3, on the keyboard, press: Control key + Shift key + Down Arrow key
- To change the format to Percent Style, in the Number group, on the Home tab,
Click
The values in column D now appear as percentages.
Using formulas and functions to complete the worksheet
Now that the VLOOKUP function has placed the discount percentage in column D, we can calculate the rest of the worksheet values.
Let's start with the gross sales total. This is the total amount of the order before the discount is applied. This can be calculated by multiplying the base price of the item by the number of units sold. The base price is in cell B1 and will not change as we copy the formula to other cells. Therefore, we will use an absolute cell reference in the formula.
- To make cell C3 the active cell,
Click in cell C3
- To calculate the gross sales, on the keyboard, type:
=$B$1*B3 Enter key
- To select cell C3,
Click in cell C3
- To copy the formula to the remaining cells,
Double-click the autofill handle
The autofill handle looks slightly different in Microsoft Excel and Google Sheets:
The gross sales for each order are now in column C.
NOTE: The results in column C may not be in currency format. We will fix this when we have completed all of the calculations.
Now let's find the discount for each order. This can be calculated by multiplying the gross sales by the % discount.
- To calculate the discount in cell E3,
Click in cell E3, type: =c3*d3 Enter key
To copy the formula to the other cells in column E,
Click in cell E3, Double-click the AutoFill handle
There is one more calculation to be added to this worksheet. We can find the net sales for each order by subtracting the discount from the gross sales. Let's put this calculation in column F.
- To calculate the discount in cell F3,
Click in cell F3, type: =c3-e3 Enter key
To copy the formula to the other cells in column F,
Click in cell F3, Double-click the AutoFill handle
With all of the calculations complete, we can see how much each order is worth.
It may be necessary to format columns C, E, and F as currency. By holding down the Control key while selecting, we can select non-contiguous columns and format them at the same time.
- To select the cells in column C,
Press & Drag from C3 to C9
- To select the cells in column E, on the keyboard, press:
Control key, Press & Drag cells E3 to F9
- To format the selected cells as currency,
In Microsoft Excel, on the Home tab, in the Number group,
, In Google Sheets, on the toolbar, Click
- To deselect the cells,
Click in a blank area of the worksheet
The columns are now formatted correctly and the worksheet is complete.