Level 3 project: order processing across four sheets

You need: VLOOKUP with exact and approximate match, INDEX and MATCH, IFERROR and ROUND. You have practised all of them in Levels 2 and 3.

The situation

An office supplies dealer has two new orders. The workbook has four sheets:

• Orders: order number, customer number, item number and quantity. This is where you work, filling in the yellow cells. The tax rate is in B5.
• Items: item number, description and net price, not sorted.
• Customers: name, town and customer number. The number is in the last column because the list comes from another system.
• Discounts: the discount tiers. Orders from 250 get 2%, from 500 get 3%, and from 1000 get 5%.

The project

What matters here

Always look prices up, never link them

The toner A-104 is in row 3 of the Items sheet, so =Items!C3 gives the right price today. As soon as someone sorts the item list or inserts an item, the formula points at a different product and the invoice is wrong, with no error to warn you. =VLOOKUP(D2,Items!A2:C7,3,FALSE) finds the number every time. That is why the exercise also checks your formulas against a re-sorted item list.

The name is to the left of the number

VLOOKUP always searches the first column of the range and returns something to the right of it. On the Customers sheet, though, the customer number is in column C and the name in column A. MATCH finds the row of the number, and INDEX returns the name from that row: =INDEX(Customers!A2:A6,MATCH(B2,Customers!C2:C6,0)). The 0 at the end of MATCH means exact match.

Discount tiers with approximate match

An amount of 774 is not in the tier table. What you want is the last tier whose lower limit is not above the amount, here 500. That is exactly what VLOOKUP does with TRUE as the fourth argument: =VLOOKUP(G2,Discounts!A2:B5,2,TRUE). For this to work, the first column must be sorted in ascending order and start at 0, otherwise small amounts return #N/A.

Gross amount in one step

The discount reduces the net amount, and tax is added to the reduced amount. G2*(1-H2) is the amount after discount, and *(1+B5) adds the tax. ROUND to 2 places makes the cell hold the amount in cents or pence that will appear on the invoice, not just look like it. The workbook uses 20% as an example rate, the standard VAT rate in the UK. In the US, sales tax depends on the state and often the city, so you would enter your own rate in B5. Keeping the rate in its own cell means a different rate only changes one cell.

Make missing items visible

Item number A-109 from order B-1002 is not in the item list. A plain VLOOKUP shows #N/A, and so does every formula that builds on it. IFERROR catches the error and writes "Item missing" instead, so whoever processes the order sees at once that the list needs updating. Once the item is added, F3 shows its price automatically.

Helper calculations

As in Excel, you may use empty cells for your own intermediate steps, for example put the amount after discount in a helper cell and refer to it in I2. Only the results in the yellow cells are checked.

If it does not work

• The message names the first cell that is not right yet. Everything before it is correct.
• If F2 shows the wrong price, FALSE is probably missing at the end of VLOOKUP. The item list is not sorted, so approximate match lands on some other item.
• If H2 shows #N/A, the discount VLOOKUP has FALSE instead of TRUE. 774 does not appear in the tier table.
• If I2 is only off after the decimal point, ROUND is missing. If I2 is far too high, the discount was not taken off or 1+B5 is missing its brackets.
• If F3 is reported as only right with this data, you typed "Item missing" by hand. The formula has to return the price as soon as the item is in the list.

FAQ