Multiple Vlookups and index, match formula

HI everyone,

I have a list of patient's names in sheet 1 as follows: In column A, I have the names, In column B, there is a test category, in column c is the test result, and in column D I have the date of the test result. I need to find, for example, the very first date when patient John's test 1 was equal or less than 3, his test 2 value was equal or less than 15, his test value for test 3 was equal or less greater than 100. In this example, the approximate date (the very first date) is around 2/1/2014 14:00 pm. My list consists of 100s names.


Patient Test Value Date
John Test 1 2.5 2/1/2015 13:00:00 PM
John Test 1 3.6 2/1/2015 17:10:00 PM
John Test 1 3.4 2/1/2015 19:18:00 PM
John Test 1 2.9 2/1/2015 23:16:00 PM
John Test 2 10 2/1/2015 14:00:00 PM
John Test 2 12 2/1/2015 15:00:00 PM
John Test 2 14 2/1/2015 16:05:00 PM
John Test 3 100 2/1/2015 22:05:00 PM
John Test 3 98 2/1/2015 13:05:00 PM
John Test 3 101 2/1/2015 17:05:00 PM
Mark Test 1 2/1/2015 13:20:00 AM
Mark Test 2
Mark test 3

Hi MCMAHOBT,

Thanks for the reply. How can I write a formula to get the first date when all the three tests making their target value? In your attached file, all I want is 13:00 pm as an answer . Thanks for you help.