Last tested date (Vlookup?)

[DjRolnik]

Hello everyone,

I am new to this forum so please correct me if I placed this thread in a wrong section or am missing some necessary info for you to help me

I have a problem with vlookup function although not sure if that's the one I really need here. I have a spreadsheet with areas tested on different dates. These areas are assigned to different releases which have their own unique names. It looks something like this:

Release Title State Closed Date
January_1 Test Area 1 To Do
February_2 Test Area 1 Closed 2016-11-28
February_2 Test Area 2 Closed 2016-11-27
March_3 Test Area 1 Closed 2016-11-28

What I want to achieve is to have one cell display the last closed date for a particular Release so that for February Release it would display the date 2016-11-28 and if there is a new item for February which is closed later than that, it would then display that later date. Other cells for corresponding releases would display the corresponding dates as well and so on.

I can have the data sorted by the last Closed Date but if an item is not closed, then in case of using the vlookup it will display a blank cell which messes up the formula.
I used some MAX formulas, INDEX ones, and nothing really worked.

Thanks a lot for your help

 

[Special-K99]

Sorting is not required.

given the Release value in H1

=MAX(IF((A1:A4=H1)*(C1:C4="Closed"),F1:F4))
should give the last Closed date

Array formula, use Ctrl-Shift-Enter

 

[DjRolnik]

Hey,

Thanks for your quick reply. The formula now does work as it should.

Thank you!