Vlookup by splitting data

[ANKUSHRS1]

Hi, Good Afternoon,

I have data column where value divided by "/" .
I want to get value of second column by using vlookup or something without hurting original data.
I have enclosed example file to give better picture
Please give me a formula based solution.

 

[shrivallabha]

You can use popular LOOKUP approach like below in cell C2 and copy down:
=LOOKUP(2^15,SEARCH(TRIM(B2),Data!$B$2:$B$7,1),Data!$C$2:$C$7)

 

[bosco_yip]

Try,

1] Criteria in Column B

2] In Column C "Result 1" C2, formula copied down :

=LOOKUP(1,-FIND(B2,Data!$B$2:$B$7),Data!$C$2:$C$7)

Regards
Bosco

 

[rahulshewale1]

Hii @ANKUSHRS1 ,

=INDEX(Data!$C$2:$C$7,MATCH("*"&B2&"*",Data!$B$2:$B$7,))


Regard
Rahul shewale