Find phrase and look up corresponding cell value

Belinda

Board Regular
Joined
Apr 5, 2004
Messages
61
I have three columns.
Column 1: A list of items with a very long text description in each cell
Column 2: The Key phrase found within the long description in the cells of Column 1.
(The key phrase is different for each cell).
Column 3: Custom shortened description in text format.
Column 4: Formula to return the Custom shortened description, equivalent to the item in Column 1

How do I create a formula in Column 4 so that Excel searches for the Key phrase found within the string of Column 1 and returns the custom description in Column 4?

Thank you
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi Belinda

Can you give us some real examples of exactly what you want to do ?
If you don't want to share real data, can you make up some data that is functionally similar to your real data ?
 
Upvote 0
Thank you Stan for directing me to the site so that I can show an example.

What I am looking for is: A formula that will give the short description in the "Final Table" using the Key Phrase and Short Description in the "Look up Table".

Your patience is much appreciated.
Belinda


Excel Workbook
AB
1Look up Table*
2Key PhraseShort Description
3EucalyptusEucalyptus product
4EucalyptusEucalyptus product
5John WhiteLiterature
6JuiceNutrition
7BarNutrition
8**
9**
10Final Table*
11Long DescriptionShort Description
12Eucalyptus lotion packaged in 3 boxesEucalyptus product
13Cream with eucalyptus and menthol for pain reliefEucalyptus product
14Food allergies by John WhiteLiterature
15Juice Complete from HerbaliteNutrition
16Power bar with potassium from MegastoreNutrition
17Mulitvitamin juiceNutrition
18Patch with EucalyptusEucalyptus product
Sheet1
 
Upvote 0
How many "key phrases" could there be ? Just these 4, or very many ?
What if the long description includes 2 or more key phrases, e.g.
"Juice allergies by John White" ?
 
Upvote 0
Hi Gerald,
The long descriptions have only one key phrase.

Hi Aladin,
The formula worked beautifully! Can you explain this part of the formula (LOOKUP(9.99999999999999E+307).
I have also come across an error, and I can't figure out what the problem is.
Thank you.

Excel Workbook
AB
1Look up Table*
2Key PhraseShort Description
3EucalyptusEucalyptus product
4EucalyptusEucalyptus product
5John WhiteLiterature
6JuiceNutrition
7BarNutrition
8**
9**
10Final Table*
11Long DescriptionShort Description
12Eucalyptus lotion packaged in 3 boxesEucalyptus product
13Cream with eucalyptus and menthol for pain reliefEucalyptus product
14Food allergies by John WhiteLiterature
15Juice Complete from HerbaliteNutrition
16Power bar with potassium from MegastoreNutrition
17Mulitvitamin juice#N/A
18Patch with EucalyptusEucalyptus product
Sheet1
 
Upvote 0
Hi Gerald,
The long descriptions have only one key phrase.

Hi Aladin,
The formula worked beautifully! Can you explain this part of the formula (LOOKUP(9.99999999999999E+307).
I have also come across an error, and I can't figure out what the problem is.
Thank you.
...

Would you clear A6 and retype Juice in that cell?
 
Upvote 0
Hi Gerald,
The long descriptions have only one key phrase.

Hi Aladin,
The formula worked beautifully! Can you explain this part of the formula (LOOKUP(9.99999999999999E+307).
I have also come across an error, and I can't figure out what the problem is.
Thank you.

Excel Workbook
AB
1Look up Table*
2Key PhraseShort Description
3EucalyptusEucalyptus product
4EucalyptusEucalyptus product
5John WhiteLiterature
6JuiceNutrition
7BarNutrition
8**
9**
10Final Table*
11Long DescriptionShort Description
12Eucalyptus lotion packaged in 3 boxesEucalyptus product
13Cream with eucalyptus and menthol for pain reliefEucalyptus product
14Food allergies by John WhiteLiterature
15Juice Complete from HerbaliteNutrition
16Power bar with potassium from MegastoreNutrition
17Mulitvitamin juice#N/A
18Patch with EucalyptusEucalyptus product
Sheet1

Hey,You Can Also Try This

LOOKUP(1E+300,SEARCH(A$3:A$7,A12),B$3:B$7)

Or You Can Use this UserDefined Function

Public Function MatchMe(Phrase As String, match As Range, offsetvar As Long)
MatchMe = "No Match"
Dim Looper As Variant
For Each Looper In match
If InStr(Phrase, Looper.Value) <> 0 Then
MatchMe = Looper.Offset(0, offsetvar).Value
Exit For
End If
Next



End Function


While Using the UserDefined Function Your Key Word Should be Same means both are proper or Upper or Lower Case

Ex:
A10=Mulitvitamin juice, B10= Juice ( See the Difference the First Letter of Juice) in A10 it is juice but in B10 it is in Proper Case i.e. Juice. If in A10 it is Multivitamin juice and in B10 it is Juice the Function Will Show NO MATCH


Type this Userdefined Function

=Matchme(A12,$A$3:$A$7,1)

I am not a Expert Just want to Share my Experience
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top