Match partial string

[djamieson18]

Hi Folks,

I require some assistance looking up a partial string using the match function.

I know how to use wildcards as follows:
=MATCH("*"&E1&"*",$A$1:$A$1000,0)

This looks for any part of E1 in range A1 to A1000 if E1 was "dav" and A1 was "dave" then it would return 1

But it falls over when E1 contains more than any cell in the range. e.g if E1 was "dave j" and A1 was "dave" it would return N/A

So my question is how to use the match function (or any other method) to find any entry of the array was partially contained in the value.

e.g.

cell A1 = joe
cell A2 = john
Cell A3 = jane

Cell B1 = joe bloggs

I require a function that identifies that all of cell A1 is contained within B1 and returns the col number.

Thanks,

Dave

 

[Aladin Akyurek]

Hi Folks,

I require some assistance looking up a partial string using the match function.

I know how to use wildcards as follows:
=MATCH("*"&E1&"*",$A$1:$A$1000,0)

This looks for any part of E1 in range A1 to A1000 if E1 was "dav" and A1 was "dave" then it would return 1

But it falls over when E1 contains more than any cell in the range. e.g if E1 was "dave j" and A1 was "dave" it would return N/A

So my question is how to use the match function (or any other method) to find any entry of the array was partially contained in the value.

e.g.

cell A1 = joe
cell A2 = john
Cell A3 = jane

Cell B1 = joe bloggs

I require a function that identifies that all of cell A1 is contained within B1 and returns the col number.

Thanks,

Dave

Try...

=LOOKUP(9.99999999999999E+307,SEARCH($A$1:$A$3,B1),$A$1:$A$3)

This will returns an A-value which occurs in B1 if at all.

 

[T. Valko]

Hi Folks,

I require some assistance looking up a partial string using the match function.

I know how to use wildcards as follows:
=MATCH("*"&E1&"*",$A$1:$A$1000,0)

This looks for any part of E1 in range A1 to A1000 if E1 was "dav" and A1 was "dave" then it would return 1

But it falls over when E1 contains more than any cell in the range. e.g if E1 was "dave j" and A1 was "dave" it would return N/A

So my question is how to use the match function (or any other method) to find any entry of the array was partially contained in the value.

e.g.

cell A1 = joe
cell A2 = john
Cell A3 = jane

Cell B1 = joe bloggs

I require a function that identifies that all of cell A1 is contained within B1 and returns the col number.

Thanks,

Dave

Try this...

Book1
	A	B	C
1	Sam	Joe Bloggs	4
2	Frank	_	_
3	Lisa	_	_
4	Joe	_	_
5	Pat	_	_
6	Erin	_	_
Sheet1

This formula entered in C1:

=LOOKUP(1E100,SEARCH(A1:A6,B1),ROW(A1:A6))

Note that if B1 contain more than 1 keyword the formula will match the keyword that is found nearest the bottom of the keyword list.

 

[T. Valko]

Try this...

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 77px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Sam</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Joe Bloggs</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Frank</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Lisa</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Joe</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Pat</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Erin</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">_</TD></TR></TBODY></TABLE>


This formula entered in C1:

=LOOKUP(1E100,SEARCH(A1:A6,B1),ROW(A1:A6))

Note that if B1 contain more than 1 keyword the formula will match the keyword that is found nearest the bottom of the keyword list.

If you want the result to be the matching word then use this formula:

=LOOKUP(1E100,SEARCH(A1:A6,B1),A1:A6)

Book1
	A	B	C
1	Sam	Joe Bloggs	Joe
2	Frank	_	_
3	Lisa	_	_
4	Joe	_	_
5	Pat	_	_
6	Erin	_	_
Sheet1

 

[djamieson18]

Thanks for your help guys!!

The above is exactly what I was looking for!!