SOLVED

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

Copper Contributor

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

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 Replies
best response confirmed by Mahmed687 (Copper Contributor)
Solution

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

``=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.

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

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)

1 best response

Accepted Solutions
best response confirmed by Mahmed687 (Copper Contributor)
Solution

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

``=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.