|
One way;
=SUMPRODUCT(($A$3:$A$4<=A9)*($B$3:$B$4=A9)*($C$3: $C$4=$B9),D3:D4) with A9 containing 4/3/2000, B9 containing John, C9 containing the formula above. "dom" wrote in message ... 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 |
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,MA TCH(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 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- dom wrote in message ... 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 |
|
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" wrote in message ... 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,MA TCH(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 -- Rgds Max xl 97 --- GMT+8, 1?22' N 103?45' E xdemechanik <atyahoo<dotcom ---- dom wrote in message ... 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 |
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,MA TCH(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)) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
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,MA TCH(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)) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- <dom wrote in message ... 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 |
| All times are GMT +1. The time now is 09:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com