I am helping with a spreadsheet and have had little success in trying
to come up with a solution. Here's the problem.

I will need the ability to have the formula reference a range of
dates based on a max for each date of incident and return and answer.

Here's and an example:

Lets say I have 3 entries
Name date of incident Average Monthly Wage
John Doe 01/01/2008 4333.
Jane Doe 01/01/2003 2888
Sally Doe 01/01/2009 3600

In the example above I would need to have the average monthly wage
column reference a range or table that included a series of date
ranges that set a max average monthly wage depending on the date
range which coincides with the date of incident, and return the actual
wage if not greater than the max for the period

below is an example of the table the formula would reference

if date of injury is:

01/01/2008-12/31/2008 max average monthly wage = 1385
01/01/1979-12/31/2003 max average monthly wage = 2000
01/01/2009-12/31/2009 max average monthly wage=3600

Thanks in advance