Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
dom
 
Posts: n/a
Default how to use lookup a value by date for criteria in Excel sheet

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







  #2   Report Post  
Dave R.
 
Posts: n/a
Default

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









  #3   Report Post  
Max
 
Posts: n/a
Default

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









  #4   Report Post  
 
Posts: n/a
Default

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











  #5   Report Post  
Max
 
Posts: n/a
Default

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
----




  #6   Report Post  
Max
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
Inserting the date that an excel sheet is saved into a cell sharock Excel Discussion (Misc queries) 2 January 6th 05 08:07 PM
Lookup Access data in Excel Chris Kellock Excel Worksheet Functions 1 December 28th 04 02:51 PM
Hyperlink to specific sheet in Excel Web File jd17 Links and Linking in Excel 0 December 8th 04 10:03 PM
Creating a Date Selector in Excel VBA? Mark Excel Discussion (Misc queries) 0 November 25th 04 11:59 PM


All times are GMT +1. The time now is 02:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"