Finding Row Number for a Date

[rwmill9716]

Cell CF8 = MATCH(Date(Year(CF5), Month(CF5), Day(CF5)), $CG:$CG)
Cell CF9 = MATCH(Date(Year(CF6), Month(CF6), Day(CF6)), $CG:$CG)

I'm reading dates in CF5 and CF6, and then finding the row number in column CG where these dates can be found. Row 9 is the last appearance of 3/1/2018 and Row 15 the last appearance of 3/31/2018. What I need is a formula for cell CF9 that identifies the first appearance of 3/1/2018, i.e., 8 in cell CF8.

Excel 2012
	CF	CG
5	3/1/2018
6	3/31/2018
7		2/29/18
8	9	3/1/2018
9	15	3/1/2018
10		3/31/2018
11		3/31/2018
12		3/31/2018
13		3/31/2018
14		3/31/2018
15		3/31/2018
HMD

 

[Eric W]

Try:

	CF	CG
2/29/18

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]5[/TD]
[TD="align: right"]3/1/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3/1/2018[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]3/1/2018[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/31/2018[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3/31/2018[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3/31/2018[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/31/2018[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/31/2018[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/31/2018[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]CF8[/TH]
[TD="align: left"]=LOOKUP(2,1/($CG$7:$CG$15=CF5),ROW($CG$7:$CG$15))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]CF11[/TH]
[TD="align: left"]=MATCH(CF5,$CG$7:$CG$15,0)+ROW($CG$7)-1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




I'm not sure why you're using YEAR/MONTH/DAY to split your date apart, then DATE to put it back together. I might recommend the LOOKUP formula instead, in case you have dates not in order.

And another thought, why do you need the row number? To feed into another formula? If so, what is it? There may be a better way than using a row number.

 

[rwmill9716]

Try:

	CF	CG
2/29/18

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]5[/TD]
[TD="align: right"]3/1/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3/31/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3/1/2018[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]3/1/2018[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/31/2018[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3/31/2018[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3/31/2018[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/31/2018[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/31/2018[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3/31/2018[/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]CF8[/TH]
[TD="align: left"]=LOOKUP(2,1/($CG$7:$CG$15=CF5),ROW($CG$7:$CG$15))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]CF11[/TH]
[TD="align: left"]=MATCH(CF5,$CG$7:$CG$15,0)+ROW($CG$7)-1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




I'm not sure why you're using YEAR/MONTH/DAY to split your date apart, then DATE to put it back together. I might recommend the LOOKUP formula instead, in case you have dates not in order.

And another thought, why do you need the row number? To feed into another formula? If so, what is it? There may be a better way than using a row number.

Thanks, Eric,

This works.

Ric