Lookup Text in various row and result text in other column

[Raghu]

Need to know the formulae for extracting particular text in other column while using VLOOKUP.

Example :attached file

 

[Haseeb A]

Hello Raghu,

Hre is one way:

=LOOKUP(100^100,SEARCH(E$2:E$5,A2),E$2:E$5)

If yoy want to look for CASE sensitive change SEARCH to FIND.

 

[Raghu]

Thank you so much Haseeb.... it works oooohhooo. Can I know "100^100" denotes?

 

[Sam Mathai Chacko]

It denotes a very LARGE number

Lookup finds the largest number in the array, if all numbers are smaller than the lookup value itself. For example,

=LOOKUP(100^100,{1,2,3,4,5,50},{10,12,14,16,18,20}) will return 20, while

=LOOKUP(5,{1,2,3,4,5,50},{10,12,14,16,18,20}) will return 18.

HTH