Making a Partial Match Between Cells

[teefy]

Try as I might, I cannot fathom this...

I have a series of keys obtained from a selection matrix, which look like this:

<TABLE style="WIDTH: 163pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=217><COLGROUP><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1170" width=32><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f3f2f5; BORDER-LEFT: #f3f2f5; BACKGROUND-COLOR: #d8d8d8; WIDTH: 139pt; HEIGHT: 15pt; BORDER-TOP: #f3f2f5; BORDER-RIGHT: #f3f2f5" class=xl66 height=20 width=185>Key</TD><TD style="BORDER-BOTTOM: #f3f2f5; BORDER-LEFT: #f3f2f5; BACKGROUND-COLOR: #d8d8d8; WIDTH: 24pt; BORDER-TOP: #f3f2f5; BORDER-RIGHT: #f3f2f5" class=xl68 width=32>

ABC​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f3f2f5; BORDER-LEFT: #f3f2f5; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: #f3f2f5; BORDER-RIGHT: #f3f2f5" class=xl65 height=20>1=10001</TD><TD style="BORDER-BOTTOM: #f3f2f5; BORDER-LEFT: #f3f2f5; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #f3f2f5; BORDER-RIGHT: #f3f2f5" class=xl69>

A​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f3f2f5; BORDER-LEFT: #f3f2f5; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: #f3f2f5; BORDER-RIGHT: #f3f2f5" class=xl65 height=20>1=10002,2=90005</TD><TD style="BORDER-BOTTOM: #f3f2f5; BORDER-LEFT: #f3f2f5; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #f3f2f5; BORDER-RIGHT: #f3f2f5" class=xl69>

A​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f3f2f5; BORDER-LEFT: #f3f2f5; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: #f3f2f5; BORDER-RIGHT: #f3f2f5" class=xl65 height=20>1=10003</TD><TD style="BORDER-BOTTOM: #f3f2f5; BORDER-LEFT: #f3f2f5; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #f3f2f5; BORDER-RIGHT: #f3f2f5" class=xl69>

B​

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f3f2f5; BORDER-LEFT: #f3f2f5; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: #f3f2f5; BORDER-RIGHT: #f3f2f5" class=xl65 height=20>1=10004,4=PB</TD><TD style="BORDER-BOTTOM: #f3f2f5; BORDER-LEFT: #f3f2f5; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #f3f2f5; BORDER-RIGHT: #f3f2f5" class=xl69>

B​

</TD></TR></TBODY></TABLE>
​

The keys are comprised of one or more values, separated by commas, and are of variable length.

I need to compare these keys against a table of data, which has a column containing ALL possible key combinations:

<TABLE style="WIDTH: 225pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=300><COLGROUP><COL style="WIDTH: 225pt; mso-width-source: userset; mso-width-alt: 10971" width=300><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f3f2f5; BORDER-LEFT: #f3f2f5; BACKGROUND-COLOR: #d8d8d8; WIDTH: 225pt; HEIGHT: 15pt; BORDER-TOP: #f3f2f5; BORDER-RIGHT: #f3f2f5" class=xl66 height=20 width=300>Matrix Key</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f3f2f5; BORDER-LEFT: #f3f2f5; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: #f3f2f5; BORDER-RIGHT: #f3f2f5" class=xl65 height=20>1=10004,2=90002,3=Apples,4=PB,5=GB,6=12345</TD></TR></TBODY></TABLE>
​

Is there a lookup/match formula I can use to establish that the "Matrix Key" contains ALL the elements in the fourth "Key" (above)?

I want to assign the "Matrix Key" with a B classification, even though there is not an exact match.

Thank you in advance!

 

[teefy]

Hmmm, OK I decided a UDF would be the way to go to solve this issue, but now I'm stuck on that too

New thread to follow!

 

[alansidman]

Do a search on this forum on 'fuzzy match.' I recall seeing that someone had already written something that may serve your purpose.

Found it:
http://www.mrexcel.com/forum/showthread.php?t=195635&highlight=fuzzy+match
Alan