Multiple IFs + Date Range Then Return X

[flash33181]

I have a table with the following information in it: Hospital Name, Type of Visit, Start Date, End Date, and Rate.

There are 8 different Hospitals, 3 different Visit Types, varying Start/End dates for each Hospital/Visit Type, and then of course a specific rates corresponding to the previous criteria.

So for example, let's say

NYU Medical Center, ER Visit, 12/1/08 to 6/30/09 = $300

The problem is the date ranges are different for each. So, in the example above, NYU Medical Center might have 11 different date ranges to pick from to get the correct rate for ER service. But they might only have 5 different date ranges to pick from to get the correct rate for Surgery Services.

I'm trying to figure out if there is a way for me to just enter the Hospital Name, Type of Service, and a Date of Service, and basically go "If Hospital name = x, Type of service = y, and Date Rage is between 'a' and 'b' then rate = z." -- if they were all uniform date ranges, this would be much easier it seems. But because I'm not only dealing with different amounts of date ranges, but also different actual date ranges themselves, this seems much more tricky.

Just to provide a further example, one set of date ranges for one hospital and visit type is:

12/1/2008 6/30/2009
7/1/2009 11/30/2009
12/1/2009 12/31/2009
1/1/2010 6/30/2010
7/1/2010 12/31/9999 (present)

And for the SAME hospital, but DIFFERENT Service Type, they are:

12/1/2008 12/31/2008
1/1/2009 6/30/2009
7/1/2009 11/30/2009
12/1/2009 12/31/2009
1/1/2010 6/30/2010
7/1/2010 3/31/2011
4/1/2011 6/30/2011
7/1/2011 12/31/9999 (present)

Thanks in advance as always.

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[flash33181]

This is pretty close to what I'm working on. I have the hospital names and service types in dropdown boxes on another sheet. I just want to be able to pick Hospital A and Service Type B and enter in Date and if it falls between X & Y, then the rate = Z.

Is this possible? Thank you.