Check singe value in Sentence and return corresponding value

[mmmarks]

Hi Team, I'm trying to search list of words in sentence, If that word is found in sentence return the corresponded search value.

Eg :
[TABLE="width: 642"]
<colgroup><col><col><col span="4"><col><col></colgroup><tbody>[TR]
[TD]Col A[/TD]
[TD]ColB[/TD]
[TD] ColC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Col G[/TD]
[TD]Col H[/TD]
[/TR]
[TR]
[TD]password is not working[/TD]
[TD]1 [/TD]
[TD]D1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Searching Words[/TD]
[TD]Display[/TD]
[/TR]
[TR]
[TD]computer has problem[/TD]
[TD]1[/TD]
[TD]D3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Password[/TD]
[TD]D1[/TD]
[/TR]
[TR]
[TD]I m not using internet[/TD]
[TD]1 [/TD]
[TD]D4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Printer[/TD]
[TD]D2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]computer[/TD]
[TD]D3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Internet[/TD]
[TD]D4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]I have formula so far in Col B which is returing 1 if word is found =IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($G$3:$G$6,A2)))))>0,"1","")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
What I need, if Password is found in sentence then D1 should be displayed in Column B
If Computer is found then D3 should be displayed in Column B.

Please help

 

[Marcelo Branco]

Try

C2 copied down
=LOOKUP(9.99E+307,SEARCH(G$3:G$6,A2),H$3:H$6)

M.

 

[mmmarks]

Try

C2 copied down
=LOOKUP(9.99E+307,SEARCH(G$3:G$6,A2),H$3:H$6)

M.

Thank you It s working as expected . Could you please explain me what is the role of 9.99E+307 and how it works ? If you have time plz.

Thanks
A

 

[Marcelo Branco]

See an excellent explanation provided by Aladin Akyurek at
https://www.mrexcel.com/forum/excel...alue-unsorted-data-post492425.html#post492425

9.99E+307 is a short of 9.99999999999999E+307 that is the Largest allowed positive number in Excel

To see what the formula does, step by step, try Formulas > Evaluate formula

M.