lookup containing specific words

[Anand_N]

Hi there,
i have a list of address in Sheet 1 Column A,
also i have a list of only states in Sheet 2 Column A
i want to check if the state names in Sheet 2 Column A are present in Sheet 1 Column A

below is the example...

Sheet 1 Column A

1 Maharashtra, india 400086 Tel No 400 056 45
2 Gujrat,India 400096
3 Maharashtra, india 400095
and do on

Sheet 2 Column A
1 Maharastra
2 Goa
3 Gujrat
and so on

Output

Sheet 1 Column A Column B

1 Goa, india 400086 Tel No 400 056 45 Goa
2 Gujrat,India 400096 Gujrat
3 Maharashtra, india 400095 #N/A


Please help me with a solution...i need this often

Thanks,

 

[Marcelo Branco]

Please, check your data and output.

How did you find this output line?
1 Goa, india 400086 Tel No 400 056 45 Goa

There is no Goa state in any row in Sheet1.

M.

 

[Anand_N]

that was just an example sir...i was not very specific...but the idea is to extract all records matching with goa or any "x" text or number value.

 

[Anand_N]

Some one pls reply

 

[Aladin Akyurek]

In B1 of Sheet1 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(","&Sheet2!$A$1:$A$3&",",","&SUBSTITUTE(A1," ","")&","),
   Sheet2!$A$1:$A$3)