If specific text contained in cell then Vlookup. Need formula not VBA.

[Mehmud Khan]

I got some difficulties while working on a small project.
Hope, anyone can help me in this matter.


In column "A" I have mentioned some specific text, If that specific word found in the note (in column "D"), than use the Vlookup from table - A2:B13

Example: (refer attached data file)

Note - In this line, flight is mentioned. Hence its correct way and go ahead for further reference.

In note - flight (as per column A)

So, in note we can see the flight word, hence first loop is correct.
Now, we have to move ahead and find the lookup value for that text, which is - 02 Advanced purchase

 

[Naresh Yadav]

Hi,

try this one in your E column

=LOOKUP(2,1/SEARCH($A$2:$A$13,D2),$B$2:$B$13)

 

[Mehmud Khan]

omg!!
this is exactly what I was expecting.....
Thanks a lot Naresh for your instant reply.