A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old January 11th, 2005, 03:39 AM
external usenet poster
 
Posts: n/a
Default how to use lookup a value by date for criteria in Excel sheet

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











Ads
  #2  
Old January 11th, 2005, 03:52 AM
Max
external usenet poster
 
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$100A3)*(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 atyahoodotcom
----


  #3  
Old January 11th, 2005, 04:22 AM
Max
external usenet poster
 
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$100A3)*(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 atyahoodotcom
----
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
QDE (Quick Date Entry) Norman Harker General Discussion 3 September 3rd, 2004 08:00 AM
Excel 2000 vs 2002 Sheet protection & Validation cells Andre Croteau General Discussion 4 July 17th, 2004 03:31 PM
when import EXCEL to ACCESS, the date format excel column title changed... Amolin General Discussion 6 June 25th, 2004 08:56 AM
Lookup and Offset (Excel 2000) Bill J. Worksheet Functions 4 February 3rd, 2004 09:15 AM


All times are GMT +1. The time now is 01:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.