Finding cell data when condition is true

[BLuse]

I have two different spread sheets with categories and I want to pull the category ID of sheet B into sheet A. While they have similar names they are not identical so I cannot do a simple VLOOKUP or other similar method. What I want to do is search if any part of the category name in sheet A is found in sheet B then pull in the corresponding category ID

Example:

Sheet A
[TABLE="class: outer_border, width: 272"]
<tbody>[TR]
[TD]Category[/TD]
[TD]CategoryID[/TD]
[/TR]
[TR]
[TD]Skin Care[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]First Aid and Healing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Moisturizers[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet B
[TABLE="class: outer_border, width: 258"]
<tbody>[TR]
[TD]Category[/TD]
[TD]CategoryID[/TD]
[/TR]
[TR]
[TD]Hair & Skin Care[/TD]
[TD="align: right"]13908[/TD]
[/TR]
[TR]
[TD]Other First Aid[/TD]
[TD="align: right"]11779[/TD]
[/TR]
[TR]
[TD]Body Lotions & Moisturizers[/TD]
[TD="align: right"]11841[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

In Sheet1 cell C2 you can test following array formula

=INDEX(Sheet2!$B$2:$B$4,MATCH(TRUE,ISNUMBER(SEARCH(LEFT(A2,9),Sheet2!$A$2:$A$4)),0))

Hope this will help

 

[Special-K99]

@BLuse: James006 solution will only work if the first 9 characters of Sheet A Category are in Sheet B, though the solution works on all your example data.

 

[BLuse]

Hello,

In Sheet1 cell C2 you can test following array formula

=INDEX(Sheet2!$B$2:$B$4,MATCH(TRUE,ISNUMBER(SEARCH(LEFT(A2,9),Sheet2!$A$2:$A$4)),0))

Hope this will help

Thank for trying to help but I somehow can't get it to work I get #N/A instead of the ID.

 

[James006]

For an Array Formula ...

Instead of the Enter key ...

You need to use simultaneously the three keys : Control Shift Enter

Hope this will help