Match lookup based on value within long text field and return category - all help appreciated!

[step_hen711]

Hey all,

I have a formula query which has been wrecking my brain for the last few days - I've searched high and low for a solution, but with no luck.

I have an excel file with 2 sheets - one contains the data and all the metrics I analyse. I'm looking to create a new metric field, so that I can split analysis into defined groups. I want to use the unique product key as the reference - within a typical cell, it contains the name of the category I'd like to define. For example, an entry in the product key is displayed as so:

"BV_Social_CommentApproved" - in this example, "Social" is one of the groups I wish to define. In sheet 2 of the file, I have a list of values (2 columns - one column contains part of the lookup value and the second column contains the category I'd like to call it.) The table had one heading of "key word2 - in this example it's "social_" and the category I wish to generate is in the second column "Social"

<tbody>
</tbody>

I require a formula solution which is able to pick out this key word within the unique product key and match it with the table in the second sheet - it must return the matched value within a new metric column.

I hope this makes sense - I'll keep an eye on this thread for any help - all help/suggestions are much appreciated!

 

[gaz_chops]

Can you post an example of sheet 1 & 2, with the results you expect, it will help to understand what you want to achieve.

 

[step_hen711]

Can you post an example of sheet 1 & 2, with the results you expect, it will help to understand what you want to achieve.

Thanks Garry - here is a quick view of each sheet and example what i want to achieve:

Sheet 1:

Unique product ID	Segment
BV_Social_CommentApproved	Social
Gift_List_2014ABC	Gift List
Shops_Abandon_Basket_1	Abandon Basket
Sheet 2: Unique product ID New Segment Social_ Social Gift_List Gift List Abandon_Basket Abandon Basket <colgroup><col><col></colgroup><tbody> </tbody>

<colgroup><col><col></colgroup><tbody>
</tbody>

Basically, I need the formula to see if part of the text in sheet 2, column 1, exists within the text of sheet 1 column 1 - if it generates a successful match, then return the value from sheet 2, column 2 within the Segment column. This would allow me to assign a Segment to each email type.

Thank you for your help!

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[step_hen711]

Thanks Garry - here is a quick view of each sheet and example what i want to achieve:

Sheet 1:

Unique product ID	Segment
BV_Social_CommentApproved	Social
Gift_List_2014ABC	Gift List
Shops_Abandon_Basket_1	Abandon Basket
Sheet 2: Unique product ID New Segment Social_ Social Gift_List Gift List Abandon_Basket Abandon Basket <tbody> </tbody>

<tbody>
</tbody>

Basically, I need the formula to see if part of the text in sheet 2, column 1, exists within the text of sheet 1 column 1 - if it generates a successful match, then return the value from sheet 2, column 2 within the Segment column. This would allow me to assign a Segment to each email type.

Thank you for your help!

<tbody>
</tbody>

Is anyone able to help?

 

[gaz_chops]

Good guess with the name!

Try
=INDEX(Sheet1!$B$2:$B$4,MATCH("*"&A2&"*",Sheet1!$A$2:$A$5,0))

 

[step_hen711]

Good guess with the name!

Try
=INDEX(Sheet1!$B$2:$B$4,MATCH("*"&A2&"*",Sheet1!$A$2:$A$5,0))

Thanks Gaz - I've been messing around with this formula for an hour this morning but with no luck - this has really tested me! Are there any alternatives I could try?

Very grateful for all your help!

 

[gaz_chops]

What do you mean, no luck? With the data above it returns the same results that you have.

 

[sanrv1f]

try this

=INDEX(Sheet2!$B$2:$B$4,MATCH(1,1/ISNUMBER(SEARCH(Sheet2!$A$2:$A$4,A2)),0))

confirm the formula with Ctrl+Shift+Enter

 

[gaz_chops]

Sankar, that is searching sheet 2 from sheet 2?

 

[sanrv1f]

Assuming the data is in Sheet1 A2, enter the formula in B2