VLOOKUP or MATCH and/or INDEX to extract value on a given date

[Don Kline]

I need to do a lookup in which I am get the date and value of a transaction
from a continuous listing of dates.

Entire range for the data is R2C1:R1215C10
Column 1 has the dates starting with 3/16/2006 and end 5/29/2009. The dates
in this column mark an actual transaction.

Column 2 has the value I need to look up - the value of the transacation

Column 9 has the dates on a continuous daily basis - starting with
3/16/2006, next date is 3/17/2006, etc.

Column 10 is where I want the result of the looked up values

So if the next transaction is on 4/1/2006, the value of that transaction
($950.40) will be in Column 10 on that matching day of 4/1/2006.

I've tried puttering around with Match and Index but the solution is eluding
me at this point.

 

[Eduardo]

Hi,
In column 10 enter

=SUMPRODUCT(--(RC[-1]=C[-9]),C[-8])

 

[Max]

Assuming real dates in the source col A, and in I2 down
In J2: =INDEX(B:B,MATCH(I2,A:A,0))
Copy down

If you need an error trap, use this in J2:
=IF(ISNA(MATCH(I2,A:A,0)),"",INDEX(B:B,MATCH(I2,A:A,0)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Luke M]

In column 10:

=SUMIF(C[-9],RC[-1],C[-8])

 

[Don Kline]

Works like a champ. Thank you.

In column 10:

=SUMIF(C[-9],RC[-1],C[-8])
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

I need to do a lookup in which I am get the date and value of a transaction
from a continuous listing of dates.

Entire range for the data is R2C1:R1215C10
Column 1 has the dates starting with 3/16/2006 and end 5/29/2009. The dates
in this column mark an actual transaction.

Column 2 has the value I need to look up - the value of the transacation

Column 9 has the dates on a continuous daily basis - starting with
3/16/2006, next date is 3/17/2006, etc.

Column 10 is where I want the result of the looked up values

So if the next transaction is on 4/1/2006, the value of that transaction
($950.40) will be in Column 10 on that matching day of 4/1/2006.

I've tried puttering around with Match and Index but the solution is eluding
me at this point.

 

[Don Kline]

Thanks for the reply. I could not get it to work. When walking it through the
Evaluate Formula, it was throwing a NUM! error in --(RC[-1]=C[-9]. I have not
been succesful in making any adustment.

Hi,
In column 10 enter

=SUMPRODUCT(--(RC[-1]=C[-9]),C[-8])

I need to do a lookup in which I am get the date and value of a transaction
from a continuous listing of dates.

Entire range for the data is R2C1:R1215C10
Column 1 has the dates starting with 3/16/2006 and end 5/29/2009. The dates
in this column mark an actual transaction.

Column 2 has the value I need to look up - the value of the transacation

Column 9 has the dates on a continuous daily basis - starting with
3/16/2006, next date is 3/17/2006, etc.

Column 10 is where I want the result of the looked up values

So if the next transaction is on 4/1/2006, the value of that transaction
($950.40) will be in Column 10 on that matching day of 4/1/2006.

I've tried puttering around with Match and Index but the solution is eluding
me at this point.

 

[Don Kline]

Thanks for your reply. I tried the one with the Error Trap. It works!

 

[Max]

Welcome, glad to hear.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik