find the newest record for each item from a list

Question

I am monitoring certain information about a list of items. the information comes in regularly however it isn't always valid. I have a formula that tells me whether the information is valid. the data is structured in the following way.

The item List| the info on AB|the info onCD|The DATEValidity info AB|Validity info CD|

ITEM001 | DATA001 |   DATA02 |  DATE1  | Yes Valid         | Not Valid         |  
------------------------------------------------------------------------------
ITEM001 | DATA003 |   DATA04 |  DATE2  | NOT Valid         | YES Valid         | 
------------------------------------------------------------------------------ 
ITEM002 | DATA005 |   DATA06 |  DATE3  | NOT Valid         | Not Valid         | 
------------------------------------------------------------------------------ 
ITEM001 | DATA007 |   DATA08 |  DATE4  | YES Valid         | YES Valid         |  
------------------------------------------------------------------------------
ITEM002 | DATA009 |   DATA10 |  DATE5  | YES Valid         | Not Valid         |  
------------------------------------------------------------------------------
ITEM005 | DATA011 |   DATA12 |  DATE6  | NOT Valid         | YES Valid         |  
------------------------------------------------------------------------------
ITEM003 | DATA013 |   DATA14 |  DATE7  | YES Valid         | Not Valid         |  
------------------------------------------------------------------------------

I would like an easy way for me to arrange the table so that I can find out for each item the last Valid Info1 with its date and the last Valid info2 with its date.

My company has a policy against saving VBA files, so unless this it is absolutely necessary I would like to be able to do this using only formulas.

Answer 1

Ok try this with or without sorting the dates:

I assumed that your data looks like this:

In Cell H2 use this formula:

=INDEX(B$1:B$7,MATCH(MAX(IF(E$1:E$7="Yes Valid",D$1:D$7)),D$1:D$7,0))

In Cell I2 use this formula:

=INDEX(D$1:D$7,MATCH(MAX(IF(E$1:E$7="Yes Valid",D$1:D$7)),D$1:D$7,0))

In Cell H5 use this formula:

=INDEX(C$1:C$7,MATCH(MAX(IF(F$1:F$7="Yes Valid",D$1:D$7)),D$1:D$7,0))

In Cell I5 use this formula:

=INDEX(D$1:D$7,MATCH(MAX(IF(F$1:F$7="Yes Valid",D$1:D$7)),D$1:D$7,0))

Above are all Array Formulas entered by pressing Ctrl+Shift+Enter.
Hope this works for you.