The pay rates are increased first of march each year. They can also be changed at any time, and if an official is upgraded the rates also increase. The rates depend on whether the gme being officiated is a regular season game or a finals game. There are also several competition types: Junior Domestic, Senior Domestic, After School Domestic, Miniball Domestic and then finals rates for each of those competitions.

Rates for U9 boys are different for U9 girls because the U9 girls is classed as a different competition type.

As if this wasn't bad enough, the rate is different for the last 3 games on a Saturday, and all games on a Sunday. (+$4 and +$1 respectively).

The only solution I've come up with so far is a massive table for every day of the year for the last 10 years and for the next 10 years (Hopefully I will still be officiating in 10 years). That's huge!

My preference is to use an effective date and match on age, day, gender, competition type and whether regular season or finals.

I've looked around for commercial and open source software but haven't come across anything. I also have 10 years of game data already recorded in Excel that could potentially be a huge task getting into some other program.

I'm assuming this is going to be a [H|V]Lookup but I'm damned if I can figure out the correct formula.

If the game date is > last pay rate row, use the last row.

If it's less than the date of the last row, I need to find the row between two dates that this game date sits between.

If the game date is < the first pay rate row - throw an error

Can any one help me figure this out?

thanks