how to use lookup a value by date for criteria in Excel sheet

[dom]

for example

*table 1

A B C D
1 Start End User Rate
2 ----- ---- ----- -----
3 1.1.2000 31.12.2000 John $200
4 1.2.2001 31.12.2001 John $300


*table 2

A B C
1 Date User Rate
2 ----- ---- -----
3 3.4.2000 John ??? <--- preferred $200 as table1 "D3"

I want to get user "John" the Rate by the criteria between Start and End
from table 1, how can I do.

thanks

(e-mail address removed)

 

[Dave R.]

One way;

=SUMPRODUCT(($A$3:$A$4<=A9)*($B$3:$B$4>=A9)*($C$3:$C$4=$B9),D34)

with A9 containing 4/3/2000, B9 containing John, C9 containing the formula
above.

 

[Max]

One way ..

Assuming table 1 is in Sheet1, table 2 is in Sheet2

In Sheet2
----------
Put in the formula bar for C3:

=IF(OR(A3="",B3=""),"",INDEX(Sheet1!$D$3:$D$100,MATCH(1,(Sheet1!$A$3:$A$100<
=A3)*(Sheet1!$B$3:$B$100>=A3)*(Sheet1!$C$3:$C$100=B3),0)))

Array-enter the formula with CTRL+SHIFT+ENTER,
instead of just pressing ENTER

Copy C3 down if desired, to return corresponding results
for other pairs of values in cols A and B

Adapt the ranges to suit

 

[Guest]

Thank you for your help, it seem function one of part. I also prefer when I
change sheet2 "A2" value to 3.2.2001, the sheet2 "C3" value will be changed
to "$300"

thanks
dom

 

[Max]

Sorry, the formula was insufficiently tested it seems ..
(notified by the OP in a private email)

Perhaps better to amend the array formula in Sheet2's C3 to:

=IF(OR(A3="",B3=""),"",INDEX(Sheet1!$D$3:$D$100,MATCH(1,(YEAR(Sheet1!$A$3:$A
$100)=YEAR(A3))*(Sheet1!$A$3:$A$100<A3)*(Sheet1!$B$3:$B$100>=A3)*(Sheet1!$C$
3:$C$100=B3),0)))

Added one more criteria to help ensure unique matching
to suit the source data format:
(YEAR(Sheet1!$A$3:$A$100)=YEAR(A3))

 

[Max]

Sorry, the formula was insufficiently tested it seems ..

Perhaps better to amend the array formula in Sheet2's C3 to:

=IF(OR(A3="",B3=""),"",INDEX(Sheet1!$D$3:$D$100,MATCH(1,(YEAR(Sheet1!$A$3:$A
$100)=YEAR(A3))*(Sheet1!$A$3:$A$100<A3)*(Sheet1!$B$3:$B$100>=A3)*(Sheet1!$C$
3:$C$100=B3),0)))

Added one more criteria to help ensure unique matching
to suit the source data format:
(YEAR(Sheet1!$A$3:$A$100)=YEAR(A3))