Find minimum date of matched value once greater than another date

K

kissina

New Member
Joined
May 25, 2011
Messages
5
I have two sheets.

Sheet 1
A B
Number Date

On Sheet 2 I have the same make up with all of the numbers in column A on sheet 1 also on sheet 2, but multiple records.

Sheet 2
A B
Number Date

What I need to do is in column C on sheet 1, return the minium date in Column B on sheet 2 once it is larger than the date in column B on sheet 1 and the number in column A sheet 2 matches the number in column A on sheet 1.

eg.
Sheet 1
A B
169 27-Jan-2011

Sheet 2
A B
169 24-Jan-2011
170 27-Jan-2011
169 30-Jan-2011
169 20-Feb-2011

the formula in Sheet 1 Column C would return 30-Jan-2011
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
kissina said:
I have two sheets.

Sheet 1
A B
Number Date

On Sheet 2 I have the same make up with all of the numbers in column A on sheet 1 also on sheet 2, but multiple records.

Sheet 2
A B
Number Date

What I need to do is in column C on sheet 1, return the minium date in Column B on sheet 2 once it is larger than the date in column B on sheet 1 and the number in column A sheet 2 matches the number in column A on sheet 1.

eg.
Sheet 1
A B
169 27-Jan-2011

Sheet 2
A B
169 24-Jan-2011
170 27-Jan-2011
169 30-Jan-2011
169 20-Feb-2011

the formula in Sheet 1 Column C would return 30-Jan-2011
Click to expand...
Control+shift+enter, not just enter:
Rich (BB code): 
=INDEX(Sheet2!$B$2:$B$5,MATCH(MIN(IF(Sheet2!$A$2:$A$5=A2,
   IF(Sheet2!$B$2:$B$5>B2,Sheet2!$B$2:$B$5))),IF(Sheet2!$A$2:$A$5=A2,
    IF(Sheet2!$B$2:$B$5>B2,Sheet2!$B$2:$B$5),0)))
 
Upvote 0
Thanks for replying so quick! I don't seem to be getting the correct answer though it's giving me mainly one date (Feb 4, 2011) - not sure why, this doesn't seem like an error date, nor is it the max or min date - any ideas? I know it's difficult to figure out why if you don't have the problem right in front of you...
 
Upvote 0
I figured out one formula which gets me halfway there:
=MIN(INDEX('Sheet2'!B1:B5,MATCH('Sheet1A1,'Sheet2'!A1:A5,0)))

It gives me the matching dates, but it doesn't take into account that it needs to be greater than the date on sheet 1 - any ideas?
 
Upvote 0
kissina said:
Thanks for replying so quick! I don't seem to be getting the correct answer though it's giving me mainly one date (Feb 4, 2011) - not sure why, this doesn't seem like an error date, nor is it the max or min date - any ideas? I know it's difficult to figure out why if you don't have the problem right in front of you...
Click to expand...

Did you apply control+shift+enter? You need to press down the control and shift keys while you hit the enter key...

Sheet2, A2:B5

<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=130><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2360" width=66><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 50pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=66> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>169</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>24-Jan-11</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>170</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>27-Jan-11</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>169</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>30-Jan-11</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 align=right>169</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=right>20-Feb-11</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR></TBODY></TABLE>

Sheet1, A2:C3

<TABLE style="WIDTH: 217pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=289><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2702" width=76><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3015" width=85><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=76> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=85> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>169</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>27-Jan-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>30-Jan-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>169</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>14-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>20-Feb-11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65> </TD></TR></TBODY></TABLE>
 
Last edited:
Upvote 0
Sheet 1:
<TABLE style="WIDTH: 170pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=225><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 52pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl81 height=17 width=69>136</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 56pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79 width=74>27-Mar-11</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 62pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl82 width=82>4-Feb-11</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl81 height=15>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl83>16-Jan-11</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82>4-Feb-11</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl81 height=15>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79>27-Feb-11</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl82>4-Feb-11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl81 height=17>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl79>15-May-11</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82>4-Feb-11</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl81 height=15>156</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79>9-Jan-11</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl82>4-Feb-11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl81 height=17>156</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl79>23-Jan-11</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82>4-Feb-11</TD></TR></TBODY></TABLE> Formula in column c is
{=INDEX('Sheet2'!$B$2:$B$2634,MATCH(MIN(IF('Sheet2'!$A$2:$A$2634=A4,IF('Sheet2'!$B$2:$B$2634>B4,'Sheet2'!$B$2:$B$2634))),IF('Sheet2'!$A$2:$A$2634=A4,IF('Sheet2'!$B$2:$B$2634>B4,'Sheet2'!$B$2:$B$2634),0)))}

Sheet 2
<TABLE style="WIDTH: 147pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=196><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 53pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl79 height=16 width=71>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 94pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82 width=125>31-Jan-11</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79 height=16>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl82>31-Jan-11</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl79 height=16>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82>2-Feb-11</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79 height=16>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl82>2-Feb-11</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl79 height=16>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82>2-Feb-11</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79 height=16>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl82>19-May-11</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl79 height=16>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82>19-May-11</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79 height=17>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl82>19-May-11</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl79 height=16>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82>19-May-11</TD></TR></TBODY></TABLE>

So on sheet 1 c2 should return 31 Jan 2011, c3 should be 19 May 2011, etc.
 
Upvote 0
kissina said:
Sheet 1:
<TABLE style="WIDTH: 170pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=225><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 52pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl81 height=17 width=69>136</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 56pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79 width=74>27-Mar-11</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 62pt; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl82 width=82>4-Feb-11</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl81 height=15>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl83>16-Jan-11</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82>4-Feb-11</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl81 height=15>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79>27-Feb-11</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl82>4-Feb-11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl81 height=17>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl79>15-May-11</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82>4-Feb-11</TD></TR><TR style="HEIGHT: 11.25pt; mso-height-source: userset" height=15><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 11.25pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl81 height=15>156</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79>9-Jan-11</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl82>4-Feb-11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl81 height=17>156</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl79>23-Jan-11</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82>4-Feb-11</TD></TR></TBODY></TABLE>Formula in column c is
{=INDEX('Sheet2'!$B$2:$B$2634,MATCH(MIN(IF('Sheet2'!$A$2:$A$2634=A4,IF('Sheet2'!$B$2:$B$2634>B4,'Sheet2'!$B$2:$B$2634))),IF('Sheet2'!$A$2:$A$2634=A4,IF('Sheet2'!$B$2:$B$2634>B4,'Sheet2'!$B$2:$B$2634),0)))}

Sheet 2
<TABLE style="WIDTH: 147pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=196><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 53pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl79 height=16 width=71>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 94pt; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82 width=125>31-Jan-11</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79 height=16>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl82>31-Jan-11</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl79 height=16>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82>2-Feb-11</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79 height=16>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl82>2-Feb-11</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl79 height=16>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82>2-Feb-11</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79 height=16>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl82>19-May-11</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl79 height=16>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82>19-May-11</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl79 height=17>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" class=xl82>19-May-11</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; HEIGHT: 12pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl79 height=16>148</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; FONT-FAMILY: Arial; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #ece9d8; FONT-WEIGHT: 400; BORDER-RIGHT: #ece9d8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" class=xl82>19-May-11</TD></TR></TBODY></TABLE>

So on sheet 1 c2 should return 31 Jan 2011, c3 should be 19 May 2011, etc.
Click to expand...

Right...

D2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=SUM(IF(Sheet2!$A$2:$A$10=A2,IF(Sheet2!$B$2:$B$10>B2,1)))

C2, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IF(D2,INDEX(Sheet2!$B$2:$B$10,MATCH(MIN(IF(Sheet2!$A$2:$A$10=A2, 
    IF(Sheet2!$B$2:$B$10>B2,Sheet2!$B$2:$B$10))),IF(Sheet2!$A$2:$A$10=A2, 
      IF(Sheet2!$B$2:$B$10>B2,Sheet2!$B$2:$B$10),0))),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Find 1st instance in column A, paste corresponding value from column B into another cell and continue down until end of data
Replies
2
Views
81
MrsFraser07
M
J
  • Solved
INDEX-MATCH-MATCH help!
2
Replies
15
Views
227
henrik2h
H
F
  • Solved
Copy paste from one Excel workbook to another with same sheet names and update dates in range
Replies
8
Views
529
Skiier89
S
S
  • Question Question
Multiple columns lookup
Replies
0
Views
281
Sipirili2504
S
E
  • Solved
Lookup the corresponding value for a match when a cell value falls between cell dates
Replies
3
Views
491
myall_blues
myall_blues

Forum statistics

Threads
1,224,522
Messages
6,179,297
Members
452,903
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top