A 1,000-gallon tank serving one person can go a decade between pump-outs; the same tank under a family of six needs service almost every year. Put the standard interval table in the sheet and VLOOKUP reads the right answer off the household size.
Four people on a 1,000-gallon tank land on the 3-year row. Change the count to six and the same formula returns 1.
The example
Three properties on the left, all with 1,000-gallon tanks; the standard interval table sits in columns E and F.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Property | People | Interval | People | Years | |
| 2 | Lakeside | 4 | 3 | 1 | 12 | |
| 3 | Cabin | 2 | 6 | 2 | 6 | |
| 4 | Duplex | 6 | 1 | 3 | 4 |
The formula
VLOOKUP takes the people count, finds its row in the table, and returns the years from the second column:
How it works
Four arguments, each doing one job:
B2is the value to find — the number of people using the system.$E$2:$F$7is the interval table, locked with dollar signs so it does not drift when the formula is copied down.2tells VLOOKUP to return the value from the second column of that table — the years.FALSEdemands an exact match, so a household of 4 returns the 4-person interval and nothing close to it.
Bigger tanks stretch the interval. Keep a table per common tank size and point the formula at the one that matches the property.
Try it: interactive demo
Pick the number of people on a 1,000-gallon tank.
Variations
Fall back to the nearest smaller size
Approximate match (TRUE) lets you enter any count and land on the next row down — keep the table sorted ascending.
Next service date
Add the interval in years to the last pump-out date.
Pitfalls & errors
Leaving off FALSE lets VLOOKUP return an approximate match on an unsorted table, which quietly hands back the wrong interval. Always state the fourth argument.
These intervals assume no garbage disposal and normal water use. A disposal or a water softener discharging to the tank shortens the cycle — drop one column for those homes.
Practice workbook
Frequently asked questions
Where do these intervals come from?
Why not just pump every three years for everyone?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class