Help with finding unique word in column from a list

[dave84dd]

Hi,

I'm stumped here. I think this can be done. Well I'm sure it can be done. I just don't know how to do it. What I am looking for is a formula to do a unique word lookup and match. I have a simple sample. Keep in mind the "list of Unique Words" will be several hundred in length. otherwise I think it could be done with an IF statement if it was only a couple long. I believe it might be index however I'm not 100% sure, which is why I am hoping some excel GURU can toss me the answer.




[TABLE="class: grid, width: 1605"]
<tbody>[TR]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]K[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]N/A[/TD]
[TD]Description[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]Need Formula to look at column C and determine unique word From Sheet 2[/TD]
[TD]Vlookup to return Unique Word Value[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]List of Unique Words[/TD]
[TD]Unique word value[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]The cat jumped over the moon[/TD]
[TD][/TD]
[TD][/TD]
[TD]***Formula should return CAT***[/TD]
[TD]I can do Vlookup no problem here[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]cat[/TD]
[TD]Furry[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]The dog ran to fetch the frisby[/TD]
[TD][/TD]
[TD][/TD]
[TD]***Formula should return DOG***[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]dog[/TD]
[TD]Furry[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]The frog is croaking outside[/TD]
[TD][/TD]
[TD][/TD]
[TD]***Formula should return FROG***[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fish[/TD]
[TD]slimy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]The fish swims in its bowl[/TD]
[TD][/TD]
[TD][/TD]
[TD]***Formula should return FISH***[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Frog[/TD]
[TD]noisy[/TD]
[/TR]
</tbody>[/TABLE]

 

[shift-del]

Hi

=LOOKUP(9^99,SEARCH(Sheet2!$A$2:$A$5,C2),Sheet2!$A$2:$A$5)
=VLOOKUP(F2,Sheet2!$A$2:$B$5,2,FALSE)

 

[dave84dd]

Hi Thanks for the solution. It is looking like it is returning the entire list, not the "matched" value.

Here is the Desired outcome.

Column C can have several of the same descriptions.

[TABLE="class: grid, width: 1013"]
<colgroup><col><col><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Desired outcome[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sheet 1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]N/A[/TD]
[TD]Description[/TD]
[TD]N/A[/TD]
[TD]N/A[/TD]
[TD]Formla to look at list of unique word[/TD]
[TD]Vlookup to return Unique Word Value[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]The cat jumped over the moon[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]CAT[/TD]
[TD]FURRY[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]The dog ran to fetch the frisby[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]DOG[/TD]
[TD]FURRY[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]The frog is croaking outside[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]FROG[/TD]
[TD]NOISY[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD]The fish swims in its bowl[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]FISH[/TD]
[TD]SLIMY[/TD]
[/TR]
</tbody>[/TABLE]

 

[shift-del]

It works fine in my sheet.
<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='6' style='font-family:Calibri; color:#000000; border-collapse:collapse; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='149,25pt'><col width='24pt'><col width='24pt'><col width='66,75pt'><col width='93,75pt'></colgroup><tr style='background-color:#FAFAFA'><td align='middle' colspan='6'>Arbeitsblatt mit dem Namen 'Tabelle1'</td></tr><tr style='background-color:#cacaca'><td> </td><td align='middle'>C</td><td align='middle'>D</td><td align='middle'>E</td><td align='middle'>F</td><td align='middle'>G</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='left' >Description</td><td align='left' >N/A</td><td align='left' >N/A</td><td align='left' >Unique Word</td><td align='left' >Unique word value</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='left' >The cat jumped over the moon</td><td align='right' > </td><td align='right' > </td><td align='left' >cat</td><td align='left' >Furry</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='left' >The dog ran to fetch the frisby</td><td align='right' > </td><td align='right' > </td><td align='left' >dog</td><td align='left' >Furry</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='left' >The frog is croaking outside</td><td align='right' > </td><td align='right' > </td><td align='left' >Frog</td><td align='left' >noisy</td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='left' style='border-bottom-left-radius: 0px;border-bottom-right-radius: 0px;' >The fish swims in its bowl</td><td align='right' style='border-bottom-left-radius: 0px;border-bottom-right-radius: 0px;' > </td><td align='right' style='border-bottom-left-radius: 0px;border-bottom-right-radius: 0px;' > </td><td align='left' style='border-bottom-left-radius: 0px;border-bottom-right-radius: 0px;' >Fish</td><td align='left' style='border-bottom-left-radius: 0px;border-bottom-right-radius: 0px;' >slimy</td></tr></table><br><table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; border-collapse:collapse; font-family:Calibri; font-size:11px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Zelle</td><td>Formel</td></tr><tr><td style='border-bottom-left-radius: 0px;'>F2</td><td style='border-bottom-right-radius: 0px;'><Span style='color:#222222'>=LOOKUP</Span><Span style='color:#0000DD'>(999,SEARCH</Span><Span style='color:#222222'>(Sheet2!$A$2:$A$5,C2)</Span><Span style='color:#0000DD'>,Sheet2!$A$2:$A$5)</Span><Span style='color:#222222'></Span></td></tr><tr><td style='border-bottom-left-radius: 0px;'>G2</td><td style='border-bottom-right-radius: 0px;'><Span style='color:#222222'>=VLOOKUP</Span><Span style='color:#0000DD'>(F2,Sheet2!$A$2:$B$5,2,FALSE)</Span><Span style='color:#222222'></Span></td></tr></table><table style='font-family:Arial; font-size:8px'><tr><td style='color:#333333'>Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016 </td></tr><tr><td style='color:#333333'>Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg</td></tr></table>

 

[dave84dd]

Yes it does work! It was my error!!! Awesome thank you very much!!!!