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

Category	CategoryID
Skin Care
First Aid and Healing
Moisturizers

<tbody>
</tbody>

Sheet B

Category	CategoryID
Hair & Skin Care	13908
Other First Aid	11779
Body Lotions & Moisturizers	11841

<tbody>
</tbody>

 

[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