Vlookup array formula help

[excelguy77]

Hello,

My data looks like this

Dataset A

Name Date
Sam 01/01/2014
Sam 02/01/2014
Rich 04/02/2014
Rich 04/25/2014

Dataset B

Name Date
Sam 01/10/2014
Rich 04/03/2014

I want to do a vlookup using values of Dataset B and get the closest date from dataset A by name.

So my final result should look like

Dataset Final

Name Date Date(A)
Sam 01/10/2014 01/01/2014
Rich 04/03/2014 04/02/2014

Is this doable?

 

[Somendra Misra]

Hi Excelguy77,

Considering your data in A2:B5.

Lookup Table in A8:B9. Use below array formula in C8 and copy down.

=INDEX($B$2:$B$6,MATCH(1,--(MIN(ABS(B8-IF(A8=$A$2:$A$6,$B$2:$B$6)))=ABS(B8-IF(A8=$A$2:$A$6,$B$2:$B$6))),0))

Enter with Ctrl+Shift+Enter.

Regards,