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]

VLOOKUP accepts wild card

=VLOOKUP("*cat*", A1:B20, 2, FALSE)

will return the value from column B1:B20 of the first cell in A1:A20 that contains the sub-string "cat"

e.g. "4 cats and 8 dogs"

Note that it will also find "catalog"

 

[sandhya16]

I am looking for a general formula because columnA of sheet 1 can have any value and match it in columnA of Sheet 2 having multiple strings but one of those string should match the columnA of Sheet1.

 

[sandhya16]

Sheet-1


ColumnA ColumnB
4RAT
4SYU






Sheet -2


ColumnA ColumnB
4SYTA 4RAT 4MAY 5SAT 100
4MAI 6STA 4SYU 5SYT 200

 

[Matty]

Will your lookup values always be surrounded by a space in the strings they're being looked for in?

Matty

 

[sandhya16]

yes always separated by 1 space.

https://drive.google.com/file/d/0B-tsxmcmhJW5SWVfc0pHMDJzbzg/view?usp=sharing

 

[Matty]

In Sheet1, try:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&A2&" "," "&Sheet2!A$2:A$11&" "),Sheet2!B$2:B$11)

Where the lookup value resides in A2.

Matty

 

[sandhya16]

can you please show me with the results for this formula its not clear to me

=LOOKUP(9.99999999999999E+307,SEARCH(" "&A2&" "," "&Sheet2!A$2:A$11&" "),Sheet2!B$2:B$11)

 

[mikerickson]

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

 

[sandhya16]

Its not working giving "#N/A" as result.

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