Macro to match and return data

[lalableah]

Hi everyone,

I'm trying to match the data in a column from one excel file with another column of data from a separate excel file. If there is a positive match, I would like it to return a cell data on the same row in the following column on the first excel file. I understand that this can be done with vlookup but because the data in separate excel file contains commas and thus I can't get a true match.

To visualize what i'm trying to illustrate, i'll breakdown into steps.

I would like to match the data below in excel file 1 to excel file 2.

Excel file 1
83883
89342
83603
84445
434961

Excel file 2
83883
89342, 541879
83603
522354, 84445
83985, 434961

Lets imagine that there is another column of data next to the list of data in excel file 2. If there is a positive match between excel file 1 to excel file 2, i would like to return cell next to the data in excel file 2 on the same row.. to the cell next to the data in excel file 1.

This is a pain for me as I have a column with few thousands of such data.

Please help me out with a macro for this task.

Thanks

 

[lalableah]

bumpppppppppppppp

 

[John Davis]

bumpppppppppppppp

Would VLOOKUP with a wildcard work??

=VLOOKUP("*"& A3&"*",$A$11:$A$15,2,FALSE)

 

[lalableah]

nope it doesnt work.

anyone can help?

 

[John Davis]

How about?

=INDEX(Sheet2!B$2:B$6,MATCH("*"&A2&"*",Sheet2!A$2:A$6&"",0)

Change ranges to suite.

Enter with Control-Shift-Enter

 

[lalableah]

thanks! this worked!

 

[John Davis]

thanks! this worked!

You're welcome. Glad it worked for you, and thanks for the feedback.