Excel 2007 : Finding the latest data based on 2 variables

[Dr Martin]

I suspect someone has already asked this question. If so can someone point me to the thread.

If not here goes.

I am trying to construct a table which will display the next validation date of a person/piece of eqpt based on the last test results.

The worksheet attached explains best. The test in B defines the date in E and I want to display the results in E against A and B. Obviously there can be more than one combination of A and B as more retests are added. Almost like a pivot table but with the the results displayed as the latest test date.

[NBVC]

You can probably use a Pivot Table... just select MAX of instead of COUNT of for the Field Setting.

Or you can use formula:

=MAX(IF($A$2:$A$6=$A17,IF($B$2:$B$6=B$15,$E$2:$E$6)))

confirmed with CTRL+SHIFT+ENTER and copied down and over.. you can then format cells as Custom: m/d/yyyy;-0;;@ to hide 0's

[Dr Martin]

NBVC

I knew there was probably a very simple and quick answer that I was missing (It's been a hard few weeks at work!!). Thanks for your help.

Dr M