Index, Match not working in my table

[vbanoguru]

US20060071206 <tbody> </tbody>	US20060071206, CN100549016C, CN1711275A, DE10251986, DE200210251986, EP1562964, JP2006505600, JP4494211, US7414133, WO2004041835 <tbody> </tbody>	If Value in C1R1 is present in C2R1, then value=C1R1
US20060172146 <tbody> </tbody>	US20060172146, AT526381, CN100523121C, CN1774490A, EP1620525B, JP2003132257, JP200488575, JP2006526278, JP4642016, KR956051, US2005548669, US20100019670, US7615633, US8257838, WO2004099339 <tbody> </tbody>
US20060182992	US20060182992, 2009395358, CN100551994C, CN101667626A, CN1777663A, CN200480010948, EP1629063A, JP2003157006, JP2004162849, JP200492274, JP2005310733, JP2010062577, JP2010080982, JP2010118670, JP4460952, JP5081221, KR1098807, KR20060016087, US2005551653, US2009174324, US7569692, US8211553, WO2004108857 <tbody> </tbody>

<tbody>
</tbody>

Hello Folks,
I am stuck with a index/match shenanigan. I have to find out if valle in C1R1 is present in a comma separated values in C2R1 and if it does, C3R1 should be same as C1R1. All I am getting is #N/A. How to I tackle this problem? I can not use vlookup because it does not work for cell that has more than 255 characters.

 

[jtakw]

Hi,

Is this what you mean:

Book1
	A	B	C
1	US20060071206	US200600712067, US20060071206, CN100549016C, CN1711275A, DE10251986, DE200210251986, EP1562964, JP2006505600, JP4494211, US7414133, WO2004041835	US20060071206
2	US20060172146	US20060172146, AT526381, CN100523121C, CN1774490A, EP1620525B, JP2003132257, JP200488575, JP2006526278, JP4642016, KR956051, US2005548669, US20100019670, US7615633, US8257838, WO2004099339	US20060172146
3	US20060182992	US2006018299222, 2009395358, CN100551994C, CN101667626A, CN1777663A, CN200480010948, EP1629063A, JP2003157006, JP2004162849, JP200492274, JP2005310733, US20060182992, JP2010062577, JP2010080982, JP2010118670, JP4460952, JP5081221, KR1098807, KR20060016087, US2005551653, US2009174324, US7569692, US8211553, WO2004108857	US20060182992
4	US20060172146	US200601721465, AT526381, CN100523121C, CN1774490A, EP1620525B, JP2003132257, JP200488575, JP2006526278, JP4642016, KR956051, US2005548669, US20100019670, US7615633, US8257838, WO2004099339	No Match
Sheet521

Cell Formulas
Range	Formula
C1	=IF(ISNUMBER(SEARCH(" "&A1&","," "&B1&",")),A1,"No Match")

Formula copied down.

 

[vbanoguru]

Worksheet Formulas Cell Formula C1 =IF(ISNUMBER(SEARCH(" "&A1&","," "&B1&",")),A1,"No Match") <tbody> </tbody>

<tbody>
</tbody>

Formula copied down.[/QUOTE]

Much appreciated. Sometime, simple is more effective than going complex.
But do you know why index/match did not work? This is critical because the cells will not behave so nicely. I really need to search A1 in the array of B:B and output A1,No match to target cell.

 

[jtakw]

INDEX/MATCH will not work as A1 is only a partial match of B1. And if you add wildcards to A1 for the match in B1, it will error out for my samples in Row 1 and Row 3.

 

[jtakw]

Regarding your comment above:

This is critical because the cells will not behave so nicely. I really need to search A1 in the array of B:B and output A1,No match to target cell.

Part in Red, I don't understand.
Part in Blue, that's what my formula does.

 

[vbanoguru]

Explanation- Value of A1 may not be in B1, but rather in B6 cell. I believe your formula is hard coded, so it looks for A1 in B1 cell only.

 

[jtakw]

Explanation- Value of A1 may not be in B1, but rather in B6 cell. I believe your formula is hard coded, so it looks for A1 in B1 cell only.

I am stuck with a index/match shenanigan. I have to find out if valle in C1R1 is present in a comma separated values in C2R1 and if it does, C3R1 should be same as C1R1

That is completely different than your description in OP, use this formula instead:

Book1
	A	B	C
1	US20060071206	US20060172146, AT526381, CN100523121C, CN1774490A, EP1620525B, JP2003132257, JP200488575, JP2006526278, JP4642016, KR956051, US2005548669, US20100019670, US7615633, US8257838, WO2004099339	US20060071206
2	US20060172156	US200601721465, AT526381, CN100523121C, CN1774490A, EP1620525B, JP2003132257, JP200488575, JP2006526278, JP4642016, KR956051, US2005548669, US20100019670, US7615633, US8257838, WO2004099339	No Match
3	US20060182992	US200600712067, US20060071206, CN100549016C, CN1711275A, DE10251986, DE200210251986, EP1562964, JP2006505600, JP4494211, US7414133, WO2004041835	US20060182992
4	US20060172146	US2006018299222, 2009395358, CN100551994C, CN101667626A, CN1777663A, CN200480010948, EP1629063A, JP2003157006, JP2004162849, JP200492274, JP2005310733, US20060182992, JP2010062577, JP2010080982, JP2010118670, JP4460952, JP5081221, KR1098807, KR20060016087, US2005551653, US2009174324, US7569692, US8211553, WO2004108857	US20060172146
Sheet521

Cell Formulas
Range	Formula
C1	=IF(ISNUMBER(LOOKUP(2,1/SEARCH(" "&A1&","," "&B$1:B$4&","))),A1,"No Match")

Change/adjust cell references/range as needed, formula copied down.

 

[vbanoguru]

That works!! Thanks. If you don't mind, can you please explain the section LOOKUP(2,1/SEARCH( ?

 

[jtakw]

Too late to edit.

In case you want the results shown a different way.

C1 formula (identical to my post # 7 above), results A value if found Anywhere in B Column.
D1 formula, results Value Within B, if found Anywhere in A Column.

Book1
	A	B	C	D
1	US20060071206	US20060172146, AT526381, CN100523121C, CN1774490A, EP1620525B, JP2003132257, JP200488575, JP2006526278, JP4642016, KR956051, US2005548669, US20100019670, US7615633, US8257838, WO2004099339	US20060071206	US20060172146
2	US20060172156	US200601721465, AT526381, CN100523121C, CN1774490A, EP1620525B, JP2003132257, JP200488575, JP2006526278, JP4642016, KR956051, US2005548669, US20100019670, US7615633, US8257838, WO2004099339	No Match	No Match
3	US20060182992	US200600712067, US20060071206, CN100549016C, CN1711275A, DE10251986, DE200210251986, EP1562964, JP2006505600, JP4494211, US7414133, WO2004041835	US20060182992	US20060071206
4	US20060172146	US2006018299222, 2009395358, CN100551994C, CN101667626A, CN1777663A, CN200480010948, EP1629063A, JP2003157006, JP2004162849, JP200492274, JP2005310733, US20060182992, JP2010062577, JP2010080982, JP2010118670, JP4460952, JP5081221, KR1098807, KR20060016087, US2005551653, US2009174324, US7569692, US8211553, WO2004108857	US20060172146	US20060182992
Sheet521

Cell Formulas
Range	Formula
C1	=IF(ISNUMBER(LOOKUP(2,1/SEARCH(" "&A1&","," "&B$1:B$4&","))),A1,"No Match")
D1	=IFERROR(LOOKUP(2,1/SEARCH(" "&A$1:A$4&","," "&B1&","),A$1:A$4),"No Match")

 

[jtakw]

That works!! Thanks. If you don't mind, can you please explain the section LOOKUP(2,1/SEARCH( ?

You're welcome.

The LOOKUP is looking for the value of 2 within the SEARCH results, we divide 1 by the search results, thereby ensuring the result will Never be Greater than 1, since LOOKUP can Not find 2, it returns, if any, the Last found value by the SEARCH.

This can also be accomplished by using the "Big Num" ( 9.99999999999999E+307 ) in place of 2, and do away with the 1/ part.