Easy Question for formula Gurus

[cavhooah]

Hello forum! I am an Army pilot and I am trying to create a flight log. Here is what I am trying to do.

"Read the last value in column F. If it is equal to "NG", then display the date in column A"

I want to know when my last Night Goggle flight was, (represented by NG) so I can determine how many days until I must fly again. (I am required at least one NG flight every 45 days, so if I can get this formula to work, I can get the value and add 45 days to it.)

Right now, I have the date represented as a Julian date in one column, and as the day-month-year in another set of columns, because I don't know a better way to set it up.

Enclosed is my very simplified file. Any help would be greatly appreciated!

Sincerely,
CW2 Dan Carbone
[email protected]
www.cavhooah.com

[cavhooah]

Bill (aka Ranger) from the www.ozgrid.com forums gave me this forum last night and it worked perfect!

The attached Array Formulae, which are entered by Ctrl+Shift+Enter, will show the last Day, Month and Year of the Night Goggles flight.

In cell L17:
=INDEX($A$6:$H$200,SMALL(IF($F$6:$F$200="NG",ROW($F$6:$F$200)-ROW($F$6)+1),COUNTIF($F$6:$F$200,"NG")),2)

In cell M17: =INDEX($A$6:$H$200,SMALL(IF($F$6:$F$200="NG",ROW($F$6:$F$200)-ROW($F$6)+1),COUNTIF($F$6:$F$200,"NG")),3)

In cell N17: =INDEX($A$6:$H$200,SMALL(IF($F$6:$F$200="NG",ROW($F$6:$F$200)-ROW($F$6)+1),COUNTIF($F$6:$F$200,"NG")),4)

Copy these formulae into the cells one at a time and press F2 to edit the cell, then Ctrl+Shift+Enter together to enter it.

Thanks Bill!

CW2 Dan Carbone
[email protected]
www.cavhooah.com