search for a string in cell having multiple string

[sandhya16]

i am looking for a code to search for a single cell value in multiple cell value and return the adjacent cell for eg-


in sheet 1-
A B
4F876




Sheet 2-


A B


4F876 4M4646 5GAR 100




Column B in sheet 1 should return 100 value.


Please help.

 

[mikerickson]

My bad, the first literal string should be "* ", rather than the space star I posted above.

Although, looking at your data, it looks like it would be safe to remove the spaces from both the literals.


=VLOOKUP("*" & A1 & "*", Sheet2!A1:B20, 2, FALSE)

 

[sandhya16]

this formula worked but every time i have to press enter button in order to get the output.How to resolve this issue?

My bad, the first literal string should be "* ", rather than the space star I posted above.

Although, looking at your data, it looks like it would be safe to remove the spaces from both the literals.


=VLOOKUP("*" & A1 & "*", Sheet2!A1:B20, 2, FALSE)

 

[mikerickson]

What do you mean "press the enter button"?

Yes you have to press enter when entering a value into a cell (i presume you are talking about entering into A1)

That is how Excel works, there is no way around it.

 

[sandhya16]

I had to change the calculation options to "Automatic" .It worked perfectly fine.Thanks a lot

What do you mean "press the enter button"?

Yes you have to press enter when entering a value into a cell (i presume you are talking about entering into A1)

That is how Excel works, there is no way around it.