Microsoft excel: how to match substring?

I have the following question in microsoft excel.

I have a column containing the text. And I need to search a specific text from that column;

and the match may not be exact and the column is not in sorted order.

e.g. The column is

yahoo.com

google.com

gmail.com

etc

In one of the cells, I want to write a formula/function such that if I give input as google then value of that cell shoould be google.com

I think I have explained it right.

I tried VLOOKUP (''google'', A1:A100, 1)

But it is working ONLY FOR EXACT MATCH. I want to match if google is substring of any of the cells.

It is fine if the result gives the first match if there are multiple matches.

Can you please help me?

Microsoft excel: how to match substring?free adware remover

Ignore what that other guy said because it's far more complicated than necessary for the simple task at hand. Just use a wildcard like this and it will work the way that you want:

VLOOKUP (''google*'', A1:A100, 1,0)

Microsoft excel: how to match substring?internet security 2006

I would add a column before the column of yahoo.com, google.com, etc.

Let's say ''google'' or whatever you're searching for is in A1, and yahoo.com etc. are in B2:B6.

1) Put this in cell A2:

=IF(NOT(ISERROR( SEARCH($A$1, $B$2:$B$6))),$A$1,0)

and copy down to A6.

So if ''google'' appears within any term in B2:B6, this will put ''google'' in the adjacent cell in column A.

2) Then in another cell (e.g. at B1):

=VLOOKUP(A1, $A$2:$B$6, 2, FALSE)

This will find the first instance of ''google'' in column A, and return what's in column B, which will be ''google.com''.

Good luck.

.