Copy selected columns from one excel to another based on partial search word


I need to populate file1 with values in file2 and file3 based on a key word. The key word is columnA in file1. The search word is part of the value and not exact match, eg ‘13904’ could be found in ‘LIB-NB13904-2’ and then return the targeted columns and write into file1.

The logic is
(1) Read columnA in file1, say ‘13904’
(2) Use ‘13904’ to search in file2 for the corresponding value in columnA, in this case ‘LIB-NB13904-2’
(3) Write the value in file1, columnB
(4) Again, use ‘13904’ to search in file2 for the corresponding value in columnD if columnC in file2 has ‘wireless’, in this case, columnD returns ‘90:61:AE:C0:86:D1’
(5) Write the value found, ie ‘90:61:AE:C0:86:D1’ in file1, columnC
(6) Using still the same value ‘13904’, now search file3 columnB for a row that has ‘13904’ as part of its value. Then return columnA, in this case ‘90-61-AE-C0-86-D1’ and write into column E of file1.

I have attached the files for reference, Thanks
file1.xlsx (9.6 KB)
file2.xlsx (776.2 KB)
file3.xlsx (82.5 KB)

Hello @Learning,

Which is the problem that you are getting? Have you tried to use Linq querys on the datatable?

There are a lot of examples on the forum:

Let’s us know your progress

Thanks and best regards,


Thanks @Susana !