Not sure if vlookup or index formula is needed

[DavidMWright]

Good day,

I am hoping somebody can help me.

I have 2 tabs, as shown below. I am trying to copy column C (Name) from tab2 to column C (Name) on tab1 where columns A (ID) match.

Now the above I can do using a vlookup but where I am failing is that it needs to copy the value from the row where column B (Date) on tab2 is the nearest date (same date or in future) of column B (Date) on tab1.

Tab1
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 198"]
<tbody>[TR]
[TD="class: xl65"]ID[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Date[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD][TABLE="width: 198"]
<tbody>[TR]
[TD]001A000000GISbdIAH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 157"]
<tbody>[TR]
[TD="class: xl65"]01/01/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]001A000000GISTtIAP[/TD]
[TD][TABLE="width: 157"]
<tbody>[TR]
[TD="class: xl65"]01/01/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 198"]
<tbody>[TR]
[TD]001G000000f4KfyIAE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 157"]
<tbody>[TR]
[TD="class: xl65"]01/01/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 198"]
<tbody>[TR]
[TD]001A000000GISVSIA5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 157"]
<tbody>[TR]
[TD="class: xl65"]02/01/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 198"]
<tbody>[TR]
[TD]001G000000oOGP4IAO[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 157"]
<tbody>[TR]
[TD="class: xl65"]24/01/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Tab2
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Date[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]0011600001mEulTAAS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="class: xl65"]13/07/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Adam[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]0011600001mEulTAAS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="class: xl65"]16/02/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]0011600001mEulTAAS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="class: xl65"]26/01/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Dave[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]0011600001mEulTAAS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="class: xl65"]29/06/2018[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Steve[/TD]
[/TR]
[TR]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD]0011600001nRtIxAAK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 176"]
<tbody>[TR]
[TD="class: xl65"]20/10/2017[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Michael[/TD]
[/TR]
</tbody>[/TABLE]

Hope that makes sense.

Greatly appreciate any help.

Dave

 

[Alphonse68]

Hi Dave.
Given that your data starts on A1 on both tabs, here is what I got:

=IFERROR(INDEX('T2'!C$2:C$6,MATCH('T1'!A2,'T2'!A$2:A$6,0),1),"")

Cheers
PS - Is it possible for John, Dave, and Steve to have the same ID? Also, in you sample data, none of the IDs match. Could that be the reason you can't get your formulas to work?

 

[DavidMWright]

Thanks, I will test that.

Yes they could have the same id.

The data I used was just for test purposes and clearly not the best example!

 

[DavidMWright]

Works perfectly. Thank you!

 

[DavidMWright]

Sorry just did a further test and it is using the oldest date, whereas it should use the same date or greater.

Are you able to update the formula?

 

[Alphonse68]

I don't know the situation nor the desired results, but generally speaking, having more than one person with the same ID will not give you accurate results, for you will always get the first one found--John, in this case. What if it's Dave or Steve you need for an answer? I guess you could always add the Date criteria and hopefully that doesn't coincide as well.

 

[DavidMWright]

The ID's are not name ID's. So the request is to copy the Name field where the ID's match, taking the name from the row with the closest date date match (same or greater).

I have found a similar previous question which was answered which I am attempting to tweak for my requirement.

Greatly appreciate your time and effort.