Project 01 · Party Supply
Project 01

Brenda's Wholesale Party Supply

You work for a wholesale party supply store. The accounts team has a spreadsheet listing customers, how much they have paid, and how much they still owe. Your job is to automate two things: assign a collection action based on the overdue amount, then visually highlight the rows so the team can see priority accounts at a glance.

Topic 06 · VLOOKUP Approximate Topic 03 · Conditional Formatting
Download Due.xlsx Project Brief (PDF)
Step 1
Action column — VLOOKUP approximate
Assign a collection action based on the Amount Past Due value

The action column (G) should automatically show the required next step for each customer based on their overdue amount. Build a lookup table on the same sheet (suggested: columns I and J), then reference it with VLOOKUP approximate.

The rules

Amount Past Due Action
Exactly 0No action
Greater than 0 up to 300Call
301 to 500Warning letter
More than 500Legal action

Build this lookup table on the sheet

Place it in an empty area (e.g. columns I–J). The table uses the lower boundary of each range — VLOOKUP approximate returns the action for the largest boundary that does not exceed the amount.

Lookup Table — columns I and J
Amount (col I)Action (col J)
0No action
0.01Call
301Warning letter
501Legal action
💡
Why 0.01 — not 1?
Using 0.01 ensures any amount between 0.01 and 300 (including cents) returns "Call". Using 1 would miss amounts like $0.50 — unlikely in this dataset but good practice. The 0 row catches exact zeros perfectly since VLOOKUP finds the largest boundary ≤ the amount.

Formula for the action column

= VLOOKUP(F4, $I$4:$J$7, 2, 1) ← 1 = approximate match (intentional)

Copy this formula down for all 17 customer rows. The table range is absolute ($I$4:$J$7) so it stays locked as you copy.

Check your action column Type the exact action text as it appears in your cell
CustomerAmount Past DueAction
Bay, Michael0.00
Ricci, Antonio475.69
Bailey, Matthew850.98
Campbell, Andy235.17
Count by action
ActionCount
No action
Call
Warning letter
Legal action
Step 2
Conditional Formatting — highlight by action
Colour each row based on the action assigned in Step 1

Use the action column as the source for three conditional formatting rules. The formatting should update automatically whenever the action column changes.

Call → Yellow highlight
Select the data range → New Rule → Use a formula
=$G4="Call"
Warning letter → Light blue highlight
Add a second rule on the same range
=$G4="Warning letter"
Legal action → Red highlight
Add a third rule on the same range
=$G4="Legal action"
No action → No formatting
No rule needed — leave these rows unstyled
📌
Cell highlight vs whole row
Cell only (simpler): select just the action column (G4:G20), use "Cell Value equals" type rules — no formula needed.

Whole row (more visual): select the entire data range (A4:G20), use formula rules with =$G4="Call" — the $ locks the column to G so every cell in the row checks the same action value. This is the technique from Topic 03 Advanced 01.
Previous
Topic 12 — Excel Errors