Find closed date and next one open

[trimiii]

Fellas,

I have 2000 service order by VIN's in column A (sheet1) with different closed dates (example, VIN# 111 service order completed on 1 Jun2019.
In the sheet2 I need to find if VIN 111 when is next service order open after 1 Jun or next one (see example below:

sheet1:
[TABLE="width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]VIN[/TD]
[TD]date, Service order closed[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]1 Jun2019[/TD]
[/TR]
[TR]
[TD]112[/TD]
[TD]5 Jun 2019[/TD]
[/TR]
[TR]
[TD]113[/TD]
[TD]6 Jun 2019[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]f[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]VIN[/TD]
[TD]date, open (1)[/TD]
[TD]date, open (2)[/TD]
[TD]date, open (3)[/TD]
[TD]date, open (4)[/TD]
[TD]date, open (5)[/TD]
[/TR]
[TR]
[TD]111[/TD]
[TD]1 Jun 2019[/TD]
[TD]6 Jun 2019[/TD]
[TD]12 Jun 2019[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]112[/TD]
[TD]5 Jun 2019[/TD]
[TD]6 Jun 2019[/TD]
[TD]15 Jun 19[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]113[/TD]
[TD]6 Jun 2019[/TD]
[TD]16 Jun 2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So what I need is to find out is, VIN#111 closed date (sheet1) and next date open if its same day OR next one.
example: VIN#111, closed service order 1 Jun 2019 (sheet 1), next open is 1 Jun 2019 (sheet 2) and next one on line which is 6 Jun 2019.

Looking forward for reply,

 

[Gerald Higgins]

In your sample data, the data on Sheet 2, column b, always matches the date in Sheet 1, column b, for the same VIN.
Is this ALWAYS TRUE in your data, OR, is it POSSIBLE that there may be dates in sheet 2 for a specific VIN that are EARLIER than the date in Sheet 1 col B ?
If this is ALWAYS TRUE, then the problem is relatively easy to solve with a simple lookup.
If it's not always true, it's still do-able but is a bit more complex.

 

[trimiii]

Hi Mr. Gerald Higgins,

Is this ALWAYS TRUE in your data, OR, is it POSSIBLE that there may be dates in sheet 2 for a specific VIN that are EARLIER than the date in Sheet 1 col B ? -
> YES, the vin# is always TRUE

looking forward for your reply...

 

[Gerald Higgins]

Hmm . . . this is a bit vague.

Note, I'm NOT asking

Is the vin# always TRUE ?

I don't even know what it means to say it is always true.

What I'm asking is,

Is it always the case that in sheet 2, the date in column B is always the same as the date in column B on sheet 1, for the same vin# ?

Let's assume it IS always the case.

Let's also assume that your data is laid out so that on both sheets, the first VIN# appears in cell A2.

To deal with a vin# in sheet 1 and look up the corresponding first date in sheet 2, use something like

=VLOOKUP(Sheet1!A2,Sheet2!$A$2:$F$4,2,FALSE)

and format the result with a date format.

To look up the second date for the same vin#, use something like

=VLOOKUP(Sheet1!A2,Sheet2!$A$2:$F$4,3,FALSE)

and so on.