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
.