Try:

=(MAX((INT(Sheet1!$B$2:$B$4)=Sheet2!$A$2)*(Sheet1! $C$2:$C$4)))

entered with ctrl+Shift+Enter

Sheet1 has the detail and Sheet2 the job list

In your example Mar 22 is NOT the latest date! but I still assume is the
maximum date and not the maximum job ID (i.e. 1.008 vs 1.001)

HTH

"Graham" wrote:

I have a project planner where the job # has a format x.001 thru x.999.,
where we can have multiple tasks for the same job.

I have a 2 sheets, 1 lists all the jobs that require work and 1 sheet lists
the available facilities and which jobs are currentlly active in them.

The facility sheet has colums job# (x.xxx) and a completion date and time
amongst other columns of info.

so I may have
lab# - job# - completion
1 - 1.001 - 17:01 Mar-23
2 - 2.001 - 03:42 April-5
3 - 1.008 - 01:00 Mar-22

the jobs will be inserted into the labs as they become available and the lab
sheet will not be in job order but lab order.

I want to be able to say job1 finishes at 01:00 on mar-22 ie the max of the
date column for the integer of the job column)

vlookup wont let me do that

any ideas?