Finding row number of the last date in a given month

[tom82]

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!

 

[baitmaster]

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]

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]

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