To return any matched text or numbers.

[Alice_Wong]

Hi all,

I am working on some data in excel sheet. I need to compare two cells (1 cell and 1 column) in order to get any of the text or numbers that are matched.

For e.g. Cell A1 reads "Apple Pie", I would like to compare Cell A1 with the data i have in Column B. For instance Cell B3 now reads "Pie". So the text that is matched for Cell A1 and Cell B3 is "Pie".
What is the formula that I can use so that I can get the output as "Pie"?

Please help.

Thanks!
Alice[/quote]

 

[Andrew Poulsom]

What results would you expect if B3 contained "Apple Tart" or "Plum Pie"?

 

[Alice_Wong]

If B3 is "Apple Tart", I would expect the result to be "Apple".
If B3 is "Plum Pie", I would expect the result to be "Pie".

Thanks!

 

[Peter_SSs]

And what reslt would you expect if
A1 = Apple Pie
B3 = Plum Pie
B4 = Apple Tart
B5 = Apple and Plum Pie

 

[Alice_Wong]

Column B will have only one of the below:-
Either Plum Pie or Apple Tart. I mean the data in Column B that I have will be very 'clean', my problem is how to make Column A's data to become more 'clean'.

E.g.
A1 = Apple Pie
B1 = Plum
B2 = Apple
B3 = Orange

I would not have "Pie" and "Apple" in Column B under seperate cells, the chances is very low.

So the result I expect is "Apple".

Below are some examples of my data:-
____________________________________________________________

A1 = CELETA/TOR/LUCENT
A2 = LUCENT/SUN
A3 = SOU/LUCENT
A4 = SEAGATE TECHNOLOGY
A5 = LECTRA/BOSE
A6 = SOURCE /HP
A7 = HVC/PALM

B1 = BOSE
B2 = HP
B3 = SEAGATE
B4 = PALM
B5 = LUCENT

Expected Results for:-
A1 = LUCENT
A2 = LUCENT
A3 = LUCENT
A4 = SEAGATE
A5 = BOSE
A6 = HP
A7 = PALM ____________________________________________________________

Please help.

Thanks,
Alice

 

[Andrew Poulsom]

In C1 type:

=INDEX(B$1:B$5,MATCH(TRUE,ISNUMBER(SEARCH(B$1:B$5,A1)),FALSE))

and press Ctrl+Shift+Enter, not just Enter. If correctly entered Excel will surround this formula with curly braces {}.

Copy the formula to C2:C7.

 

[Alice_Wong]

Thank you! It works now

Have a nice day!