Finding the last entry in a row with a second criteria

[mitchelljones]

Hello,

I currently have a formula which finds the last date that has been entered in a row by using OFFSET, MATCH and MAX.

I would like to be add a second criteria that shows the last entry in a row that is below a certain date. In the spreadsheet that is attached, I have data in the April columns for example 4, but would only like to show the latest entry made before the end of March.

Any help would by greatly appreciated.

Thanks

[ragulduy]

=MAX(IF(A5:M5<O$3,A5:M5,0))
in O5 as an array formula (confirm with ctrl+shift+enter)
and change the value of O3 to the 31/03/13 if you want end of march.

Then copy down as needed.

Note, for your previous formula:
=max(A5:M5)
would have worked I believe

[mitchelljones]

Excellent, thank you for your help - I knew I was making it too difficult, and there had to be an easier way.

Building on your solution in the O column, do you know how I could also populate the P column with the Reading associated with the Date now listed in column O?

[ragulduy]

=INDEX(A5:M5,MATCH(O5,A5:M5,0)+1)
In P5 and copy down

is how I would do it.

[mitchelljones]

Perfect, thank you again, extremely helpful