Finding row number of the last date in a given month

tom82

New Member
Joined
Jul 9, 2019
Messages
2
Hi,
I have the below table

Line # Date Amount
2 15/1/2019 1000
3 28/5/2019 1500
4 28/5/2019 9999
5 28/5/2019 8888

I want to extract the row number of the last date that is after 30/4/2019 and prior to 30/5/2019.
So in this case the formula would return row 5.


Any idea on how to do that?
Thanks in advance!
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,034
welcome to the board

A simple Match formula might help: =MATCH([your end date here],B:B,1)

I'm using 1 as the third argument so it returns the last row that is less than the end value, rather than an exact match. I'm not currently testing for values larger than your start date
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,952
Office Version
365, 2010
Platform
Windows
welcome to the board

A simple Match formula might help: =MATCH([your end date here],B:B,1)

I'm using 1 as the third argument so it returns the last row that is less than the end value, rather than an exact match. I'm not currently testing for values larger than your start date
That won't work because the amount of dates in May is greater than the largest value in that sample.

Perhaps:
=LOOKUP(9.99999999E+307,IF(TEXT(B2:B5,"mm/yyyy")="05/2019",B2:B5),ROW(B2:B5))

Confirm with CTRL-SHIFT-ENTER rather than just Enter.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,750
Messages
5,446,271
Members
405,393
Latest member
sully361

This Week's Hot Topics

Top