pbart,

Thanks a lot for your help on this. I understand your suggestion,
except when you say "will return a value from MATCH that provides a
starting row/date for the subsequent Resource search". At some point I
think that the formula I started to work uses some similar approach.
This is how it looks like so far:

INDEX("Sheet 2 COST",MATCH(CONCATENATE(SUMPRODUCT(--("EFFECTIVE
DATE"<="ACTVITY DATE"),--("NEXT EFFECTIVE DATE for the
resource""ACTVITY DATE"),--("EFFECTIVE DATE"),--("Sheet 2 RES
ID"="Sheet 1 RES ID")),"Sheet 1 RES ID"),"dummy: Concatenate(Effective
Date&ResID)",0))

The "NEXT EFFECTIVE DATE for the resource" is a dummy column in sheet
2, sorted by Res ID, Eff Date, then with a serie of If I can determine
the end date for the effective rate.

I know, I know, this is confusing... but looks reasonable??? Still
need to work on the N/A error handling, but that's not complex.

THANKS!
Cecilia



On Mar 24, 7:10*pm, pbart wrote:
Can you sort the second table starting with =TODAY() and running backwards?

If so searching for the Activity Date within the Effective Date range will
return a value from MATCH that provides a starting row/date for the
subsequent Resource search. You could use CONCATENATE to produce a string
representing the range to be searched, eg

=CONCATENATE("B"&(16+F3)&":C21")

(where F3 is the result of the first MATCH or the function itself). *Finally
VLOOKUP, referencing this string using INDIRECT() and set to return the
contents one column to the right of the ResID, would give the cost.



"Chechu" wrote:
I am needing some help on this situation. I have the data structured
in this way:

* *A * * * B * * * C
* *Res ID *Cost * *Activity Date
1 *1234 * * $20 * *1/1/2010
2 *1234 * * $25 * *1/20/2010
3 *7432 * * $15 * *2/2/2010
4 *2574 * * $45 * *10/1/2009
5 *7432 * * $65 * *1/2/2010

It shows by resource (Col A), Hourly Cost (Col B), and Activity date
(Col C).

Then I have a second sheet, with this format:

* *A * * * B * * * C
* *Res ID *Cost * *Effective Date
1 *1234 * * $15 * *12/30/2009
2 *1234 * * $18 * *1/18/2010
3 *7432 * * $12 * *1/31/2010
4 *2574 * * $43 * *9/29/2009
5 *7432 * * $67 * *12/31/2009

Same format, but it shows the standard cost. Effective Date represents
the day when this cost becomes available.

What I am trying to do is in the first sheet, add a column with the
current Cost (Sheet 2) at the moment of the transaction. Example:
Resource 1234, shows $20 cost on an activity on 1/1/2010, but the real
cost coming from sheet 2 is $15 (because it is since 30/12/2009 to
1/18/2010, when a new cost becomes effective).
Sheet 2 can contain two, three, four... x numbers of times the same
resource (with different effective date, of course).

Does somebody know how to calculate this??? In the meantime I am
trying with Index, Match, Sumproduct, Lookup.......

Thanks!
Cecilia
.- Hide quoted text -

- Show quoted text -