Partial Matching and copying over a separate column when a match is found

[sco315]

Hello. I have two columns I and J that have some model numbers in them. Model J has model numbers that are longer than those in column I. I want to have another column F that looks at I2 and searches column J for a partial match (7 characters) and once it finds one, it copies the date from column K into that new column.
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]New Date (F)[/TD]
[TD]Model #(I)[/TD]
[TD]New Model #(J)[/TD]
[TD]Old Date(K)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]16AKU48[/TD]
[TD]12AJO30[/TD]
[TD]4/19/2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BLS2I78V0N[/TD]
[TD]TBAB-335-2N[/TD]
[TD]6/30/2013[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]11AI249[/TD]
[TD]RCSL-O*4251[/TD]
[TD]8/13/2014[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]HDF48*G56[/TD]
[TD]SADE-042JA[/TD]
[TD]9/7/2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]OM89-KK9[/TD]
[TD]16AJL48A01[/TD]
[TD]2/18/2013[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BP017-59111[/TD]
[TD]11AI249OP9[/TD]
[TD]12/21/2011[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]354ASI[/TD]
[TD]RIFA-98710[/TD]
[TD]11/17/2013[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]RPPE-F456[/TD]
[TD]18AMYE16[/TD]
[TD]10/15/2014[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4HGP*56ER5[/TD]
[TD]41SUQ160193L[/TD]
[TD]6/10/2015[/TD]
[/TR]
</tbody>[/TABLE]

So in this example, the code entered in F4 would look at I4 (11AI249) and search all of column J until it finds something that matches the first 7 characters to I4. Once it does (J7) it copies the date adjacent to it into F4.

If someone could please help me, I would greatly appreciate it. All I have so far is =MATCH("*"&LEFT(I2,7)&"*",J2:J464,0) but that is very wrong.

 

[Maverick27]

Download & install the "NEW" Excel Fuzzy Lookup Add-in. Works like a charm - no coding required.

https://www.microsoft.com/en-us/download/details.aspx?id=15011

 

[sco315]

I appreciate the suggestion, but unfortunately it needs to be coded. The columns are actually being populated from other files and the new dates will need to update themselves whenever I open this spreadsheet too.