# Finding row number of the last date in a given month

#### tom82

##### New Member
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?

### 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
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
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.

#### tom82

##### New Member
Thanks guys! @baitmaster, I tweaked a bit the formula and seems to work just fine!