Row Number

[How_Do_I]

Hello, how would I find the last row with 04-Jan-18 please?

Excel Workbook
	A	B	C	D	E
1	01-Jan-18
2	01-Jan-18
3	01-Jan-18		1st	Last
4	02-Jan-18		02-Jan-18	04-Jan-18	Last Row
5			4		13
6
7
8	03-Jan-18
9	04-Jan-18
10	04-Jan-18
11	04-Jan-18
12	04-Jan-18
13	05-Jan-18
Sheet1

 

[How_Do_I]

Aladin, month and year. These formulas seem to be doing the trick. Both CSE...

=MIN(IF(Date_of_Death>=H7,ROW(Date_of_Death)-ROW(INDEX(Date_of_Death,1))+1))

=MAX(IF(Date_of_Death<=H7,ROW(Date_of_Death)-ROW(INDEX(Date_of_Death,1))+1))

 

[Aladin Akyurek]

Are you trying to find the relative row number of the first occurrence of a month/year date? And the last occurrence?

 

[How_Do_I]

My final goal is two search dates say 01-Jan-14 and 01-Jan-15. I want the row of the first date equal to, or greater than 01-Jan-14, (There might not be a 01-Jan-14) and the biggest date equal to or less than 01-Jan-15. All the dates are in a range called Date_of_Death and to try and simplify it, lets put 01-Jan-14 in A1 and 01-Jan-15 in A2. I hope that clears it up Aladin.

Excel Workbook
	A	B	C	D
1	01-Jan-14	01-Jan-15		01-Jan-13
2	2	4		02-Jan-14
3				02-Jan-14
4				31-Dec-14
5				02-Jan-15
6				02-Jan-15
Sheet1

 

[Aladin Akyurek]

Assuming that the dates in D are chronological...

In A2 enter:

=MATCH(INDEX($D$1:$D$6,MATCH($A1,$D$1:$D$6,1)+(LOOKUP($A1,$D$1:$D$6) < $A1)),$D$1:$D$6,0)

In B2 enter:

=MATCH($B1,$D$1:$D$6,1)

 

[SyedUsman]

=MIN(IF(A:A=D4,ROW(A:A)))

=MAX(IF(A:A=D4,ROW(A:A)))

Min to find the First Row & Max to find the last Row.

 

[How_Do_I]

Thanks Aladin. I could get your solution to work Syed but thanks for looking.

 

[Aladin Akyurek]

Thanks Aladin. I could get your solution to work Syed but thanks for looking.

You are welcome. They are by the way fast working formulas.

 

[How_Do_I]

You are welcome. They are by the way fast working formulas.

I meant that I couldn't get Syed's formula to work, So your formulas are the fast formulas yes? Sorry to confuse everyone!

 

[Aladin Akyurek]

...So your formulas are the fast formulas yes?...

Yes they are.