Hard Problem. Compare ID, then Date Ranges, return $ Value

[davisro]

Please see attached file.

Comparing timecard records to an IT Contractor salary rates file.
date range is the stated period of time a particular salary rate i
paid to a contractor. There are many thousands of timecard records t
check against the proper salary rates. I need a formula that meets th
following conditions:

1) Global ID on timecard must match that on Bill Rates file
2) If workweek end date is before the start of the 1st date range fo
that global ID, then use the salary for the first date range
3) if workweek end date is between a date range for that global ID
then use the salary for that date range
4) if workweek end date is after the last date range for that globa
ID, then use the salary from the last date range for that global id
5) if UOM (column i) is STH, return column D for that global ID an
that date rage
6) if UOM (column i) is OTH, return column E for that global ID an
that date rag

Attachment filename: array formula test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=41313

 

[Jonathan Rynd]

1) Global ID on timecard must match that on Bill Rates file
2) If workweek end date is before the start of the 1st date range for
that global ID, then use the salary for the first date range
3) if workweek end date is between a date range for that global ID,
then use the salary for that date range
4) if workweek end date is after the last date range for that global
ID, then use the salary from the last date range for that global id

You can do this with a VLOOKUP and an IF. I'm assuming the date ranges are
sorted.

Look at the documentation for VLOOKUP and see how it works. Then create a
VLOOKUP that handles #2 and #3 given #1. Once you've got that, it's just a
matter of using some IFs to handle the other cases.