finding a max date in a range in vlookup data
 

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?

finding a max date in a range in vlookup data
 

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?

finding a max date in a range in vlookup data
 

I get #value! when using that formula. Additionally I noted a mistake in my
example - row 1 is the actual maxium I was after not row 3 ie order line 1
takes longer to process than order line 8.

this is the forumla I have based on your suggestion

=MAX((INT(Labs!$D$18:$D$59)=contracts!$D62)*(Labs! $G$18:$G$59))

where labs column d is the job no.xxx contract column d is the actual whole
job and labs column G is the completion date of the order line.


"Toppers" wrote:

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?

finding a max date in a range in vlookup data
 

Note I was entering this as an array formula (the braces didnt appear in the
post)

"Graham" wrote:

I get #value! when using that formula. Additionally I noted a mistake in my
example - row 1 is the actual maxium I was after not row 3 ie order line 1
takes longer to process than order line 8.

this is the forumla I have based on your suggestion

=MAX((INT(Labs!$D$18:$D$59)=contracts!$D62)*(Labs! $G$18:$G$59))

where labs column d is the job no.xxx contract column d is the actual whole
job and labs column G is the completion date of the order line.


"Toppers" wrote:

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?

finding a max date in a range in vlookup data
 

Graham,
Worked OK for me. I returned the first item of 23rd Match.

#VALUE suggests data (type) problems.

If problem continues, post sample to toppers at
REMOVETHISjohntopley.fsnet.co.uk



"Graham" wrote:

Note I was entering this as an array formula (the braces didnt appear in the
post)

"Graham" wrote:

I get #value! when using that formula. Additionally I noted a mistake in my
example - row 1 is the actual maxium I was after not row 3 ie order line 1
takes longer to process than order line 8.

this is the forumla I have based on your suggestion

=MAX((INT(Labs!$D$18:$D$59)=contracts!$D62)*(Labs! $G$18:$G$59))

where labs column d is the job no.xxx contract column d is the actual whole
job and labs column G is the completion date of the order line.


"Toppers" wrote:

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?

finding a max date in a range in vlookup data
 

found the problem - I had a non-numeric job no in the lab sheet . It now works

thanks

"Toppers" wrote:

Graham,
Worked OK for me. I returned the first item of 23rd Match.

#VALUE suggests data (type) problems.

If problem continues, post sample to toppers at
REMOVETHISjohntopley.fsnet.co.uk



"Graham" wrote:

Note I was entering this as an array formula (the braces didnt appear in the
post)

"Graham" wrote:

I get #value! when using that formula. Additionally I noted a mistake in my
example - row 1 is the actual maxium I was after not row 3 ie order line 1
takes longer to process than order line 8.

this is the forumla I have based on your suggestion

=MAX((INT(Labs!$D$18:$D$59)=contracts!$D62)*(Labs! $G$18:$G$59))

where labs column d is the job no.xxx contract column d is the actual whole
job and labs column G is the completion date of the order line.


"Toppers" wrote:

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?