Multivariable bottom-to-top LOOKUP

In the worksheet attached, I want a formula to calculate the difference between ALARM time & RESET time for a given tag number. Every time a RESET event occurs (Column B) I want to go back to the last time that tag showed a ALARM event and display the date/time in Column C.

If I use formula =LOOKUP(2,1/(A2:A100=A16),C2:C100) in cell D16 it will locate an incorrect tag event - the one that says FORCE. Can this search be done using 2 criteria? I would want not only to search for A16 in Column A but also look for the word "ALARM" in Column B and then return the date/time value from Column C. Cell D16 should retrieve the value from cell C12 (as shown typed in manually) since that was the last time Gas1 had a ALARM event. I tried to nest a AND statement in it, =LOOKUP(2,1/AND(A2:A100=A16,B2:B100="ALARM"),C2:C100), but it didn't work.

Read the topic below for better and quicker help on your question.

http://www.excelforum.com/the-water-...-question.html