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]

Sorry, I am struggling to understand. Do you want to search for "Clearance" from Sheet2 ColA, if found in Sheet1 ColA then return "AdHoc" from Sheet2 ColB?

 

[step_hen711]

Sorry, I am struggling to understand. Do you want to search for "Clearance" from Sheet2 ColA, if found in Sheet1 ColA then return "AdHoc" from Sheet2 ColB?

Sorry, this is not the complete list for both sheets so may look slight odd - but yes, I wish to search to see if the key word (Sheet 2, Col A) is contained within the email name (Sheet1, col A) - if the email name contains the key word, then return the value listed in Sheet 2, column B.

 

[gaz_chops]

I think what you are trying to do, is to see if sheet1 cell A2 contains any words that appear in sheet2 colA then return what is in sheet 2 colB, is this correct?

 

[Aladin Akyurek]

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>

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!

B2 of Sheet1:

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

See the worbook that implements the suggestion: https://dl.dropboxusercontent.com/u/65698317/step_hen711 lookup search.xlsx

 

[gaz_chops]

Aladin your formula works as does Sankars formula on page 1, very confusing as to what the OP wants!

 

[Aladin Akyurek]

Aladin your formula works as does Sankars formula on page 1, very confusing as to what the OP wants!

I think so. I think also that Lookup\Search is probably faster. The OP has at least a file to look at.

 

[gaz_chops]

Yes the other is CSE so your's is prob faster, if it is what he wants?

 

[step_hen711]

B2 of Sheet1:

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

See the worbook that implements the suggestion: https://dl.dropboxusercontent.com/u/65698317/step_hen711 lookup search.xlsx

Wow, this works an absolute treat - I thank you all for your help and perseverance in solving this - believe me, it was driving me mad.

The suggestion was particularly helpful

Have a great day guys!

 

[Aladin Akyurek]

Wow, this works an absolute treat - I thank you all for your help and perseverance in solving this - believe me, it was driving me mad.

The suggestion was particularly helpful

Have a great day guys!

Glad to help.