Index, Match not working in my table

vbanoguru

New Member
Joined
Aug 4, 2014
Messages
18
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="width: 146"]US20060071206[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 425"]
<tbody>[TR]
[TD="width: 425"]US20060071206, CN100549016C, CN1711275A, DE10251986, DE200210251986, EP1562964, JP2006505600, JP4494211, US7414133, WO2004041835[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]If Value in C1R1 is present in C2R1, then value=C1R1[/TD]
[/TR]
[TR]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD]US20060172146[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 425"]
<tbody>[TR]
[TD="width: 425"]US20060172146, AT526381, CN100523121C, CN1774490A, EP1620525B, JP2003132257, JP200488575, JP2006526278, JP4642016, KR956051, US2005548669, US20100019670, US7615633, US8257838, WO2004099339[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]US20060182992[/TD]
[TD][TABLE="width: 425"]
<tbody>[TR]
[TD="width: 425"]US20060182992, 2009395358, CN100551994C, CN101667626A, CN1777663A, CN200480010948, EP1629063A, JP2003157006, JP2004162849, JP200492274, JP2005310733, JP2010062577, JP2010080982, JP2010118670, JP4460952, JP5081221, KR1098807, KR20060016087, US2005551653, US2009174324, US7569692, US8211553, WO2004108857[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.
tY9psKu.png

tY9psKu

OwUYM7e
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi,

Is this what you mean:


Book1
ABC
1US20060071206US200600712067, US20060071206, CN100549016C, CN1711275A, DE10251986, DE200210251986, EP1562964, JP2006505600, JP4494211, US7414133, WO2004041835US20060071206
2US20060172146US20060172146, AT526381, CN100523121C, CN1774490A, EP1620525B, JP2003132257, JP200488575, JP2006526278, JP4642016, KR956051, US2005548669, US20100019670, US7615633, US8257838, WO2004099339US20060172146
3US20060182992US2006018299222, 2009395358, CN100551994C, CN101667626A, CN1777663A, CN200480010948, EP1629063A, JP2003157006, JP2004162849, JP200492274, JP2005310733, US20060182992, JP2010062577, JP2010080982, JP2010118670, JP4460952, JP5081221, KR1098807, KR20060016087, US2005551653, US2009174324, US7569692, US8211553, WO2004108857US20060182992
4US20060172146US200601721465, AT526381, CN100523121C, CN1774490A, EP1620525B, JP2003132257, JP200488575, JP2006526278, JP4642016, KR956051, US2005548669, US20100019670, US7615633, US8257838, WO2004099339No Match
Sheet521
Cell Formulas
RangeFormula
C1=IF(ISNUMBER(SEARCH(" "&A1&","," "&B1&",")),A1,"No Match")


Formula copied down.
 
Upvote 0
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"]=IF(ISNUMBER(SEARCH(" "&A1&","," "&B1&",")),A1,"No Match")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
ABC
1US20060071206US20060172146, AT526381, CN100523121C, CN1774490A, EP1620525B, JP2003132257, JP200488575, JP2006526278, JP4642016, KR956051, US2005548669, US20100019670, US7615633, US8257838, WO2004099339US20060071206
2US20060172156US200601721465, AT526381, CN100523121C, CN1774490A, EP1620525B, JP2003132257, JP200488575, JP2006526278, JP4642016, KR956051, US2005548669, US20100019670, US7615633, US8257838, WO2004099339No Match
3US20060182992US200600712067, US20060071206, CN100549016C, CN1711275A, DE10251986, DE200210251986, EP1562964, JP2006505600, JP4494211, US7414133, WO2004041835US20060182992
4US20060172146US2006018299222, 2009395358, CN100551994C, CN101667626A, CN1777663A, CN200480010948, EP1629063A, JP2003157006, JP2004162849, JP200492274, JP2005310733, US20060182992, JP2010062577, JP2010080982, JP2010118670, JP4460952, JP5081221, KR1098807, KR20060016087, US2005551653, US2009174324, US7569692, US8211553, WO2004108857US20060172146
Sheet521
Cell Formulas
RangeFormula
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.
 
Upvote 0
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
ABCD
1US20060071206US20060172146, AT526381, CN100523121C, CN1774490A, EP1620525B, JP2003132257, JP200488575, JP2006526278, JP4642016, KR956051, US2005548669, US20100019670, US7615633, US8257838, WO2004099339US20060071206US20060172146
2US20060172156US200601721465, AT526381, CN100523121C, CN1774490A, EP1620525B, JP2003132257, JP200488575, JP2006526278, JP4642016, KR956051, US2005548669, US20100019670, US7615633, US8257838, WO2004099339No MatchNo Match
3US20060182992US200600712067, US20060071206, CN100549016C, CN1711275A, DE10251986, DE200210251986, EP1562964, JP2006505600, JP4494211, US7414133, WO2004041835US20060182992US20060071206
4US20060172146US2006018299222, 2009395358, CN100551994C, CN101667626A, CN1777663A, CN200480010948, EP1629063A, JP2003157006, JP2004162849, JP200492274, JP2005310733, US20060182992, JP2010062577, JP2010080982, JP2010118670, JP4460952, JP5081221, KR1098807, KR20060016087, US2005551653, US2009174324, US7569692, US8211553, WO2004108857US20060172146US20060182992
Sheet521
Cell Formulas
RangeFormula
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")
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top