Vlookup not working, but Find tool(ctrl+f)works

[Mahmed687]

Hello anyone who can help,

please take a look at this image.

Short question is , how can I get the value for test1 while it is inside a cell with multiple values separated with " , " or " / "?

Or maybe ,Is there a function like the Find(ctrl+f) tool that can get the value/text of the cell next to it (like 2323 in the image)?

Thanks for anyone who can help me with a function for this type of situation instead of needing to separate manually all the cells.
 

[#2 · Accepted solution]

@Mahmed687 

=INDEX(J10:J12,MATCH(1,N(ISNUMBER(SEARCH(G10,I10:I12))),0))

Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. 

=VLOOKUP(1,CHOOSE({1,2},N(ISNUMBER(SEARCH(G10,I10:I12))),J10:J12),2,FALSE)

Or with VLOOKUP. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. 

[#3]

It worked finally!!
Thanks @OliverScheurich you really saved me a lot of time ,was really hoping that the VLOOKUP function had more to give and you showed me.(with ctrl+shift+enter and with both functions just had to move the location of the data range and it is all good from my side)