Having an excel file as shown as below:

First sheet:

| Animal_with_age|
|----------------|
| Animal-Dog123L |
| at-cat234L     |
|  MS-Donkey12   |
|  occoco98L     |
| Ms-dog donkeyone|

Second sheet:-

| Animal         |
|--------------  |
| Dog            |
| CAT            |
| Donkey         |
|  coco          |
| dog donkeyone  |

I need the output as below:

sheet 1:

| Animal_with_age|Animal|
|----------------|------|
| Animal-Dog123L |Dog   |
| at-cat234L     |Cat   |
|  MS-Donkey12   |Donkey|
|  occoco98L     |coco  |
|Ms-dog donkeyone| dogdonkeyone|

Is that possible? Is that possible if i convert to dataframe?

CodePudding user response：

Using XLOOKUP & SEARCH formulas, we can do full to partial string matching...

=XLOOKUP(TRUE,ISNUMBER(SEARCH(Sheet2!$A$2:$A$5,Sheet1!A2)),Sheet2!$A$2:$A$5)

Output of sheet1 -> Paste formula in sheet1 cell b2 & drag till last row;

Hope this Helps...