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]

Mmm I think I got it the wrong way round from his initial explanation!

"the text in sheet 2, column 1, exists within the text of sheet 1 column 1"

 

[step_hen711]

Mmm I think I got it the wrong way round from his initial explanation!

"the text in sheet 2, column 1, exists within the text of sheet 1 column 1"

Thank you for your help sanrv1f - I'm not sure I'm following the logic of this formula, could you please explain? Thank you -I feel I'm nearly there!

 

[step_hen711]

Mmm I think I got it the wrong way round from his initial explanation!

"the text in sheet 2, column 1, exists within the text of sheet 1 column 1"

Hey Gaz,

Do you think the order of your initial formula was around the wrong way? Could you show me how the formula should be?

Thank you!

 

[gaz_chops]

Hi Stephen,
Your 1st comment now confuses me "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."

My initial formula was entered on sheet2, under new segment, it looked for Social_ in Col A of Sheet1 and returned Match from Segment.

Having seen Sankar's reply I thought I had misread your initial post.

 

[Aladin Akyurek]

B2 of Sheet1, just enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH("_"&Sheet2!$A$2:$A$4,"_"&$B1),Sheet2!$B$2:$B$4),"NA")

 

[step_hen711]

B2 of Sheet1, just enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH("_"&Sheet2!$A$2:$A$4,"_"&$B1),Sheet2!$B$2:$B$4),"NA")

Thank you for this formula, Aladin - I tried it out on my spreadsheet, but I couldn't get it to work.

Could you please explain what the "9.99999" etc part does? I've never seen this before in a formula!

The formula must search for the key phrase which is location in Sheet 2, column 1 - it must search to see if this key phrase is located with the value of Sheet 1, column 1 - if it contains this value, it must return the entry in Sheet 2, column 2.

The formula is based in the 3rd column of Sheet 1.

I hope this helps make it clearer and thank you all for your help to date!

 

[gaz_chops]

Stephen, does this not work? Entered in Sheet1 Col C
=INDEX(Sheet2!$B$2:$B$4,MATCH("*"&Sheet2!A2&"*",Sheet1!$A$2:$A$4,0))

 

[step_hen711]

Stephen, does this not work? Entered in Sheet1 Col C
=INDEX(Sheet2!$B$2:$B$4,MATCH("*"&Sheet2!A2&"*",Sheet1!$A$2:$A$4,0))

This didn't work I'm afraid, but I feel like I'm getting closer Garry as it matched for the value it was looking up in the MATCH("*"&Sheet2!A2&" part - within in this column, there is a list of key phrases which I need to formula to lookup against and return the correct field to Sheet 1.

 

[gaz_chops]

Can you post a slightly larger sample? The formula appears to do what you are asking for, it looks at the value in sheet 2 cell A2, searches for this value in sheet 1 col A, if it matches it returns the value from sheet 2 Col B, if this is wrong, please explain again what you are expecting as a result?

 

[step_hen711]

Can you post a slightly larger sample? The formula appears to do what you are asking for, it looks at the value in sheet 2 cell A2, searches for this value in sheet 1 col A, if it matches it returns the value from sheet 2 Col B, if this is wrong, please explain again what you are expecting as a result?

Sheet 1:

Email Name	Segment
BV_Social_CommentApproved	<<Formula>>
Gift_List_2014ABC
Shops_Abandon_Basket_1
BV_Social_ResubmitReview Sheet 2: Email lookup name Name Abandon_Basket Abandon Basket Category Category Clearance AdHoc Fashion_Female Category Gifitng AdHoc <colgroup><col><col></colgroup><tbody> </tbody>

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