Find and display the last occurance in two columns and display related cell

Hey guys,

I am looking for a formula to insert into the INSPECTIONS Last Completed table which is attached. The formula needs to display the hours when the last inspection took place for each vehicle, and as you can see it needs to look for the inspection value in both columns C and D but only display the last occurance for either one. Also in the attachment is the desired result and sample data to build the equation with. Thanks for the help!

willia97

Hi

K37: =INDEX($E$1:$E$25,MAX(IF(($B$2:$B$25=$I36)*($C$2:$C$25=K$36),ROW($B$2:$B$25),""),IF(($B$2:$B$25=$I36)*($D$2:$D$25=K$36),ROW($B$2:$B$25),""))) Note this formula is array entered (CTRL, SHIFT, ENTER). Copy across / down as required.

rylo

Willia,

This is what I came up with... very close to rylo...

=INDEX($E$2:$E$25,MAX(MAX(($B$2:$B$25=$I36)*($C$2:$C$25=K$36)*ROW($C$2:$C$25)),MAX(($B$2:$B$25=$I36)*($D$2:$D$25=K$36)*ROW($D$2:$D$25)))-1,1)

Also, needs the array-enter (CTRL-SHIFT-ENTER).

Dennis