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 :

Col A	ColB	ColC				Col G	Col H
password is not working	1	D1				Searching Words	Display
computer has problem	1	D3				Password	D1
I m not using internet	1	D4				Printer	D2
						computer	D3
						Internet	D4
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","")

<colgroup><col><col><col span="4"><col><col></colgroup><tbody>
</tbody>

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.