Partial Match Columns Data within two sheets

[satish78]

Hi Friends,

I did research related to my task but did not find exactly what I am looking
Trying to partial match Sheet1 columnA with Sheet2 ColumnA and sheet1 ColumnB with sheet2 ColumnB and get corresponding sheet2 columnC data to sheet1 columnC.

If possible, also Reverse lookup and partial match sheet2 row3 data with sheet1 row5 (because "ross land" and "Developer/engineer" with Sheet1 row5 is partial name matched)

Need VBA/Index match

Here is an example data
Sheet1

Ross Land Front-End Developer
Raj Parmar Application Architect / CTI
Shraddha Pathak Software Engineer at F.M.Howell
Mathew Rawlinson Program Director, Digital
Ross Land Developer/engineer
Aaron Nguyen Ecommerce at Deep

Sheet2
Ross Land Front-End Developer ross@gmail.com
Raj Parmar Application Architect / CTI .Net and C++… raj@gmail.com
Ross Land, CFO Developer/engineer rossj@gmail.com
Shraddha Pathak Software Engineer at F.M.Howell shraddha@gmail.com
Mathew Rawlinson Program Director, Digital TV & Applicati… mathew@gmail.com
Aaron Nguyen Ecommerce at Deep Discount Servers Aaron@gmail.com

Please help me

 

[oldbrewer]

is column A just names ?

 

[satish78]

yes, just names only.

 

[satish78]

Can anyone help me please?.

 

[oldbrewer]

[TABLE="width: 965"]
<colgroup><col><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]Ross Land Front-End Developer[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD]Ross Land[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Raj Parmar Application Architect / CTI[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD]Raj Parmar[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shraddha Pathak Software Engineer at F.M.Howell[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD]Shraddha Pathak[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mathew Rawlinson Program Director, Digital[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD]Mathew Rawlinson[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ross Land Developer/engineer[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD]Ross Land[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aaron Nguyen Ecommerce at Deep[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD]Aaron Nguyen[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aaron Nguyen Ecommerce at Deep Discount Servers Aaron@gmail.com[/TD]
[TD][/TD]
[TD="align: right"]12[/TD]
[TD]Aaron Nguyen[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mathew Rawlinson Program Director, Digital TV & Applicati… mathew@gmail.com[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD]Mathew Rawlinson[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Raj Parmar Application Architect / CTI .Net and C++… raj@gmail.com[/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[TD]Raj Parmar[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ross Land Front-End Developer ross@gmail.com[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD]Ross Land[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ross Land, CFO Developer/engineer rossj@gmail.com[/TD]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD]Ross Land[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shraddha Pathak Software Engineer at F.M.Howell shraddha@gmail.com[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD]Shraddha Pathak[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]by stripping out the names you can search for duplications[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]we have just 1 in this example[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]probably I would sort the lower table into ascending order[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]not sure what you want now[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[satish78]

https://spaces.hightail.com/receive/SEPSPmjJHx

Here is sample workbook with 2 sheets with formula.
formula works on exact match but not partial match and does not go if the ColumnA has duplicate name.

 

[oldbrewer]

sorry - I never download

 

[satish78]

That is the safe link and can be downloaded.

 

[oldbrewer]

sorry it is a matter of principle - put it on google sheets ??

 

[satish78]

This formula does "Exact Match" =INDEX(Sheet2!C:C,MATCH(1,(A1=Sheet2!A:A)*(B1=Sheet2!B:B),0))
But I need "partial Match" formula.
Can anyone modify or provide me VBA code for above task?.