Find the next date for a product
OK, it didn't work because there are duplicate item numbers on sheet2. You
didn't mention that in your post.
So:
Sheet 1
A B C
1 Item 1 3/03/2007
Sheet 2
A B
1 item 1 1/03/2007
2 item 1 4/03/2007
3 item 1 5/03/2007
Which date should be returned for item 1? Both row 2 and 3 are greater than
the date on sheet1.
--
Biff
Microsoft Excel MVP
"Algeraist" wrote in message
...
No it didn't work -
Hope I entered it correctly
I've pasted the format I tested it on the the function
=IF(COUNTIF(Sheet2!A1:A13,A1),IF(VLOOKUP(A1,Sheet2 !A:B,2,0)B1,VLOOKUP(A1,Sheet2!A:B,2,0),""),"")
Test data look like this
Sheet 1
A B C
1 Item 1 3/03/2007
2 Item 2 4/03/2007
3 Item 3 3/06/2007 4/03/2008
4 Item 4 4/03/2007
5 Item 5 5/03/2007
6 Item 6 6/03/2007
7 Item 7 7/03/2007
Sheet 2
A B
1 item 1 1/03/2007
2 item 1 4/03/2007
3 item 1 5/03/2007
4 item 2 1/03/2007
5 item 2 3/03/2008
6 item 2 3/03/2007
7 item 2 1/03/2008
8 item 3 4/03/2008
9 item 3 1/03/2008
10 item 3 2/03/2008
11 item 3 1/07/2008
12 item 3 23/03/2008
13 item 3 1/03/2008
"T. Valko" wrote:
Try this:
=IF(COUNTIF(Sheet2!A$2:A$15,A2),IF(VLOOKUP(A2,Shee t2!A$2:B$15,2,0)B2,VLOOKUP(A2,Sheet2!A$2:B$15,2,0 ),""),"")
--
Biff
Microsoft Excel MVP
"Algeraist" wrote in message
...
Hello,
I have an excel document with two sheets, sheet1 contains a list of
product
codes in column A and a date next to each in column B. Sheet2 is almost
the
same but with different dates and some products aren't on the list.
For each product code - In column C on sheet one I want to find the
same
product code from sheet2 and pick up the date next to it if it is
greater
than the date in column B. If there is no match I was going to leave
the
cell
blank.
I've tried index/match with if statements and lookups but with no
success
Any ideas.... Thanks in advance
|