Match from list a to anything in list b

[adamtj]

Hello.

I have found this forum useful for some time. This is the first time I have resorted to asking my own question as I have not had any luck finding a solution.

Problem:

I have two lists of data.
List A (root strings): a series of short alphanumeric strings.
List B: a list of long alphanumeric strings.

The point of the formula I am trying to come up with will look through "List B" row by row and find if it contains a match from "ListB"

For example:
<table border="0" cellpadding="0" cellspacing="0" width="309"><colgroup><col style="mso-width-source:userset;mso-width-alt:3254;width:67pt" width="89"> <col style="mso-width-source:userset;mso-width-alt:5705;width:117pt" width="156"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:67pt" height="20" width="89">ListA</td> <td class="xl65" style="width:117pt" width="156">ListB</td> <td class="xl65" style="width:48pt" width="64">Match</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">575</td> <td class="xl65">s575-khjfdgsh</td> <td class="xl65">TRUE</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">869</td> <td class="xl65">s424-xfcgdf</td> <td class="xl65">FALSE</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">3939</td> <td class="xl65">s869-dfgdf</td> <td class="xl65">TRUE</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl65">s575-jhgzjhgd</td> <td class="xl65">TRUE</td> </tr> </tbody></table>


The closest things I have found at nested If statements (unfeasible since listA is too long.

Match did not work because it will search List A2 for a match in List B.

I am using excel 2007.

Thank you very much for any help you can provide.


Cheers.

 

[Aladin Akyurek]

Hello.

I have found this forum useful for some time. This is the first time I have resorted to asking my own question as I have not had any luck finding a solution.

Problem:

I have two lists of data.
List A (root strings): a series of short alphanumeric strings.
List B: a list of long alphanumeric strings.

The point of the formula I am trying to come up with will look through "List B" row by row and find if it contains a match from "ListB"

For example:
<table border="0" cellpadding="0" cellspacing="0" width="309"><colgroup><col style="mso-width-source:userset;mso-width-alt:3254;width:67pt" width="89"> <col style="mso-width-source:userset;mso-width-alt:5705;width:117pt" width="156"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:67pt" height="20" width="89">ListA</td> <td class="xl65" style="width:117pt" width="156">ListB</td> <td class="xl65" style="width:48pt" width="64">Match</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">575</td> <td class="xl65">s575-khjfdgsh</td> <td class="xl65">TRUE</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">869</td> <td class="xl65">s424-xfcgdf</td> <td class="xl65">FALSE</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">3939</td> <td class="xl65">s869-dfgdf</td> <td class="xl65">TRUE</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl65">s575-jhgzjhgd</td> <td class="xl65">TRUE</td> </tr> </tbody></table>


The closest things I have found at nested If statements (unfeasible since listA is too long.

Match did not work because it will search List A2 for a match in List B.

I am using excel 2007.

Thank you very much for any help you can provide.


Cheers.

Let A2:A4 house ListA and B2:B5 ListB.

In C2 enter and copy down:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($A$2:$A$4,B2)))

 

[adamtj]

Let A2:A4 house ListA and B2:B5 ListB.

In C2 enter and copy down:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($A$2:$A$4,B2)))

Hello.

I applied the formula you provided and it returns:

<table border="0" cellpadding="0" cellspacing="0" width="309"><col style="mso-width-source:userset;mso-width-alt:3254;width:67pt" width="89"> <col style="mso-width-source:userset;mso-width-alt:5705;width:117pt" width="156"> <col style="width:48pt" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt;width:67pt" height="20" width="89">ListA</td> <td class="xl65" style="width:117pt" width="156">ListB</td> <td class="xl65" style="width:48pt" width="64">Match</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">575</td> <td class="xl65">s575-khjfdgsh</td> <td class="xl65">FALSE</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">869</td> <td class="xl65">s424-xfcgdf</td> <td class="xl65">TRUE</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">3939</td> <td class="xl65">s869-dfgdf</td> <td class="xl65">TRUE</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">
</td> <td class="xl65">s575-jhgzjhgd</td> <td class="xl65">FALSE</td> </tr> </tbody></table>

"s869-dfgdf"(B4) contains "869"(A3) so it should also state "FALSE".

Thank you for your help so far.

 

[Aladin Akyurek]

Hello.

I applied the formula you provided and it returns:

<TABLE border=0 cellSpacing=0 cellPadding=0 width=309><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 5705" width=156><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="WIDTH: 67pt; HEIGHT: 15pt" class=xl65 height=20 width=89>ListA</TD><TD style="WIDTH: 117pt" class=xl65 width=156>ListB</TD><TD style="WIDTH: 48pt" class=xl65 width=64>Match</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>575</TD><TD class=xl65>s575-khjfdgsh</TD><TD class=xl65>FALSE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>869</TD><TD class=xl65>s424-xfcgdf</TD><TD class=xl65>TRUE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" class=xl65 height=20>3939</TD><TD class=xl65>s869-dfgdf</TD><TD class=xl65>TRUE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD class=xl65>s575-jhgzjhgd</TD><TD class=xl65>FALSE</TD></TR></TBODY></TABLE>

"s869-dfgdf"(B4) contains "869"(A3) so it should also state "FALSE".

Thank you for your help so far.

<TABLE style="WIDTH: 193pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=258><COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2759" width=78><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4124" width=116><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #efefef; WIDTH: 58pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=78>ListA
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 87pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=116>ListB</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #efefef; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Match</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>575</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>s575-khjfdgsh</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=center>TRUE</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>869</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>s424-xfcgdf</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=center>FALSE</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19 align=right>3939</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>s869-dfgdf</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=center>TRUE</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>s575-jhgzjhgd</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 align=center>TRUE</TD></TR></TBODY></TABLE>

C2:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($A$2:$A$4,B2)))

copied down, yields the results the exhibit above shows. Aren't they the results you wanted? Note that the formula checks if a B-cell contains any value from A2:A4.

 

[T. Valko]

Hello.

I have found this forum useful for some time. This is the first time I have resorted to asking my own question as I have not had any luck finding a solution.

Problem:

I have two lists of data.
List A (root strings): a series of short alphanumeric strings.
List B: a list of long alphanumeric strings.

The point of the formula I am trying to come up with will look through "List B" row by row and find if it contains a match from "ListB"

For example:
<TABLE cellSpacing=0 cellPadding=0 width=309 border=0><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 5705" width=156><COL style="WIDTH: 48pt" width=64></COLGROUP><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="WIDTH: 67pt; HEIGHT: 15pt" width=89 height=20>ListA</TD><TD class=xl65 style="WIDTH: 117pt" width=156>ListB</TD><TD class=xl65 style="WIDTH: 48pt" width=64>Match</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" height=20>575</TD><TD class=xl65>s575-khjfdgsh</TD><TD class=xl65>TRUE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" height=20>869</TD><TD class=xl65>s424-xfcgdf</TD><TD class=xl65>FALSE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="HEIGHT: 15pt" height=20>3939</TD><TD class=xl65>s869-dfgdf</TD><TD class=xl65>TRUE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="HEIGHT: 15pt" height=20>

</TD><TD class=xl65>s575-jhgzjhgd</TD><TD class=xl65>TRUE</TD></TR></TBODY></TABLE>


The closest things I have found at nested If statements (unfeasible since listA is too long.

Match did not work because it will search List A2 for a match in List B.

I am using excel 2007.

Thank you very much for any help you can provide.


Cheers.

Try this...

Book1
	A	B	C
1	ListA	ListB	Match
2	575	s575-khjfdgsh	TRUE
3	869	s424-xfcgdf	FALSE
4	3939	s869-dfgdf	TRUE
5		s575-jhgzjhgd	TRUE
Sheet1

This formula entered in C2 and copied down:

=ISNUMBER(LOOKUP(1E100,SEARCH(A$2:A$4,B2)))

 

[adamtj]

Thank you both very much for your help.

This seems to have done the trick.

much appreciated.