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

 

[AlanY]

try

=LOOKUP(2,1/(A1:A13=D4),ROW(A1:A13))

 

[Aladin Akyurek]

Do you mean the row of the last occurrence of 4-Jan-18 in column A?

If so, control+shift+enter, not just enter:

=MATCH(9.99999999999999E+307,1/($A$1:INDEX(A:A,MATCH(9.99999999999999E+307,A:A))=$D4))

This yields essentially the relative row number.

 

[How_Do_I]

Thanks for that Alan and Aladin. Nice to see you still around Aladin, I hope you are well.

 

[AlanY]

you're welcome

 

[Aladin Akyurek]

Thanks for that Alan and Aladin. Nice to see you still around Aladin, I hope you are well.

How come you missed me?

Note that my suggestion does avoid testing a definite range.
Note also that you need to think about whether a native row is required or a relative one.

Regards,

Aladin

 

[How_Do_I]

I've not been around as much recently Aladin. Just saying hello especially as you have helped me out lots of times over the years which is of course greatly appreciated.

 

[Aladin Akyurek]

Ok, cristal clear. Welcome back.

 

[How_Do_I]

I have got myself a little confused now. If I used the following to find how my records of interest there are , how would I find the row of the 1st and last record of interest please. All 51 returns could be the same date in the middle of Jan - 1918 in you see what I mean.

By Year and Month

	G	H	I	J

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:83px;"><col style="width:83px;"><col style="width:83px;"><col style="width:83px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: right"]Jan-1918[/TD]
[TD="align: right"]01-Jan-18[/TD]
[TD="align: right"]31-Jan-18[/TD]
[TD="align: right"]51[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G7 =C11&"-"&$J$4 H7 =G7 +0 I7 =EOMONTH(H7,0) J7 =SUMPRODUCT(--(Date_of_Death>=H7),--(Date_of_Death<=I7)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Aladin Akyurek]

what do you have in C11 and in J4?