Need help with a formula - hlookup maybe?

M

MagnumOpus

New Member
Joined
Apr 27, 2011
Messages
17
Hi,

I'm trying to compare two arrays that each contain 4 numbers, along with another array that contains 3 numbers to find common numbers between all three arrays. The "Key Digits" are in columns R-S-T-U, the "Mirrors" are in columns Y-Z-AA-AB, and the "RBG" numbers are in columns AE-AF-AG. I'd like a 'pull-down' formula that does a comparison and displays matching numbers into columns AH-AI-AJ (labeled "Match" below). Here's the tricky part: because there are three columns to contain these "matching" numbers, there could be up to 3 digits that match, but there could also be no digits that match. If there are no digits that match, I'd like the function to leave the cells blank. There won't be duplicate values among matching digits. I've been doing this manually but there's a lot of data and I'm making mistakes due to eye strain and fatigue, so any help would be appreciated.

It looks like this:

<table style="width: 717px; height: 74px;" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:1365; width:29pt" width="38" span="3"> <col style="mso-width-source:userset;mso-width-alt:1365;width:29pt" width="38"> <col style="mso-width-source:userset;mso-width-alt:1080;width:23pt" width="30"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:910;width:19pt" width="26"> <col style="mso-width-source:userset;mso-width-alt:1365; width:29pt" width="38" span="3"> <col style="mso-width-source:userset;mso-width-alt:1365;width:29pt" width="38"> <col style="mso-width-source:userset;mso-width-alt:910;width:19pt" width="26"> <col style="mso-width-source:userset;mso-width-alt:2247;width:47pt" width="63"> <col style="mso-width-source:userset;mso-width-alt:1223; width:26pt" width="34" span="2"> <col style="mso-width-source:userset;mso-width-alt:1223;width:26pt" width="34"> <col style="mso-width-source:userset;mso-width-alt:1223; width:26pt" width="34" span="2"> <col style="mso-width-source:userset;mso-width-alt:1223;width:26pt" width="34"> </colgroup><tbody><tr style="height:14.4pt" height="19"> <td colspan="4" class="xl80" style="height:14.4pt;width:116pt" width="152" height="19">Key Digits
</td> <td class="xl77" style="width:23pt" width="30">
</td> <td class="xl77" style="width:48pt" width="64">Accuracy</td> <td class="xl72" style="border-left:none;width:19pt" width="26">
</td> <td colspan="4" class="xl81" style="border-right:.5pt solid black; border-left:none;width:116pt" width="152">Mirrors</td> <td class="xl75" style="width:19pt" width="26">
</td> <td class="xl77" style="width:47pt" width="63">Accuracy</td> <td colspan="3" class="xl76" style="border-right:.5pt solid black; width:78pt" width="102">RBG</td> <td colspan="3" class="xl82" style="border-right:.5pt solid black; border-left:none;width:78pt" width="102">Match</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl76" style="height:14.4pt" height="19">2</td> <td class="xl76">5</td> <td class="xl76">6</td> <td class="xl77">9</td> <td class="xl77">
</td> <td class="xl71">0%</td> <td class="xl72" style="border-left:none">
</td> <td class="xl76">7</td> <td class="xl76">0</td> <td class="xl76">1</td> <td class="xl77">4</td> <td class="xl75">
</td> <td class="xl71">0%</td> <td class="xl76">4</td> <td class="xl76">5</td> <td class="xl77">9</td> <td class="xl78">
</td> <td class="xl78">
</td> <td class="xl79">
</td> </tr> <tr style="height:14.4pt" height="19"> <td class="xl76" style="height:14.4pt" height="19">2</td> <td class="xl76">5</td> <td class="xl76">6</td> <td class="xl77">9</td> <td class="xl77">
</td> <td class="xl71">0%</td> <td class="xl72" style="border-left:none">
</td> <td class="xl76">7</td> <td class="xl76">0</td> <td class="xl76">1</td> <td class="xl77">4</td> <td class="xl75">
</td> <td class="xl71">0%</td> <td class="xl76">4</td> <td class="xl76">5</td> <td class="xl77">9</td> <td class="xl78">
</td> <td class="xl78">
</td> <td class="xl79">
</td> </tr> </tbody></table>
 
Sort by date Sort by votes
MagnumOpus said:
Wow, that's really close. Only problems are that it's erasing my 4,9 match and replacing it with 9,9 matches, and it's displaying a 0 where it shouldn't be.

The formula now looks like this with the actual cell references.

=IFERROR(SMALL(IF(ISNUMBER(MATCH(AE2:AG2,
IF(ISNUMBER(MATCH(Y2:AB2,R2:U2,0)),Y2:AB2,"#"),0)),AE2:AG2),
COLUMNS($AH$2:AH2)),"")

it's displaying this:

<TABLE border=0 cellSpacing=0 cellPadding=0 width=717><COLGROUP><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1365" span=3 width=38><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1365" width=38><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1080" width=30><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 910" width=26><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1365" span=3 width=38><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1365" width=38><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 910" width=26><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2247" width=63><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1223" span=2 width=34><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1223" width=34><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1223" span=2 width=34><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1223" width=34></COLGROUP><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="WIDTH: 116pt; HEIGHT: 14.4pt" class=xl84 height=19 width=152 colSpan=4>Key Digits For The Day</TD><TD style="WIDTH: 23pt" class=xl81 width=30>

</TD><TD style="WIDTH: 48pt" class=xl81 width=64>Accuracy</TD><TD style="BORDER-LEFT: medium none; WIDTH: 19pt" class=xl72 width=26>

</TD><TD style="BORDER-LEFT: medium none; WIDTH: 116pt; BORDER-RIGHT: black 0.5pt solid" class=xl85 width=152 colSpan=4>Key Digit Mirrors</TD><TD style="WIDTH: 19pt" class=xl75 width=26>

</TD><TD style="WIDTH: 47pt" class=xl81 width=63>Accuracy</TD><TD style="WIDTH: 78pt; BORDER-RIGHT: black 0.5pt solid" class=xl82 width=102 colSpan=3>RBG</TD><TD style="BORDER-LEFT: medium none; WIDTH: 78pt; BORDER-RIGHT: black 0.5pt solid" class=xl86 width=102 colSpan=3>Match</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="HEIGHT: 14.4pt" class=xl82 height=19>2</TD><TD class=xl82>5</TD><TD class=xl82>6</TD><TD class=xl81>9</TD><TD class=xl81>

</TD><TD class=xl71>100%</TD><TD style="BORDER-LEFT: medium none" class=xl72>

</TD><TD class=xl82>7</TD><TD class=xl82>0</TD><TD class=xl82>1</TD><TD class=xl81>4</TD><TD class=xl75>

</TD><TD class=xl71>0%</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #60497b; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>4</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: red; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>5</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #7030a0; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl81>9</TD><TD class=xl83>

</TD><TD class=xl83>

</TD><TD class=xl83>

</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="HEIGHT: 14.4pt" class=xl82 height=19>2</TD><TD class=xl82>5</TD><TD class=xl82>6</TD><TD class=xl81>9</TD><TD class=xl81>

</TD><TD class=xl71>0%</TD><TD style="BORDER-LEFT: medium none" class=xl72>

</TD><TD class=xl82>7</TD><TD class=xl82>0</TD><TD class=xl82>1</TD><TD class=xl81>4</TD><TD class=xl75>

</TD><TD class=xl71>0%</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #60497b; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>4</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: red; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>5</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #7030a0; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl81>9</TD><TD class=xl83>

</TD><TD class=xl83>

</TD><TD class=xl83>

</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="HEIGHT: 14.4pt" class=xl82 height=19>0</TD><TD class=xl82>5</TD><TD class=xl82>6</TD><TD class=xl81>9</TD><TD class=xl81>

</TD><TD class=xl71>67%</TD><TD style="BORDER-LEFT: medium none" class=xl72>

</TD><TD class=xl82>5</TD><TD class=xl82>0</TD><TD class=xl82>1</TD><TD class=xl81>4</TD><TD class=xl75>

</TD><TD class=xl71>67%</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #ffc000; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>8</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #7030a0; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>9</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #ddd9c3; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl81>

</TD><TD class=xl83>0</TD><TD class=xl83>

</TD><TD class=xl83>

</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="HEIGHT: 14.4pt" class=xl82 height=19>0</TD><TD class=xl82>5</TD><TD class=xl82>6</TD><TD class=xl81>9</TD><TD class=xl81>

</TD><TD class=xl71>67%</TD><TD style="BORDER-LEFT: medium none" class=xl72>

</TD><TD class=xl82>5</TD><TD class=xl82>0</TD><TD class=xl82>1</TD><TD class=xl81>4</TD><TD class=xl75>

</TD><TD class=xl71>33%</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #ffc000; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>8</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #7030a0; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>9</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #ddd9c3; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl81>

</TD><TD class=xl83>0</TD><TD class=xl83>

</TD><TD class=xl83>

</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="HEIGHT: 14.4pt" class=xl82 height=19>2</TD><TD class=xl82>5</TD><TD class=xl82>6</TD><TD class=xl81>9</TD><TD class=xl81>

</TD><TD class=xl71>33%</TD><TD style="BORDER-LEFT: medium none" class=xl72>

</TD><TD class=xl82>7</TD><TD class=xl82>0</TD><TD class=xl82>1</TD><TD class=xl81>4</TD><TD class=xl75>

</TD><TD class=xl71>33%</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: red; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>5</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #ffc000; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>8</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #7030a0; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl81>9</TD><TD class=xl83>

</TD><TD class=xl83>

</TD><TD class=xl83>

</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="HEIGHT: 14.4pt" class=xl82 height=19>2</TD><TD class=xl82>5</TD><TD class=xl82>6</TD><TD class=xl81>9</TD><TD class=xl81>

</TD><TD class=xl71>67%</TD><TD style="BORDER-LEFT: medium none" class=xl72>

</TD><TD class=xl82>7</TD><TD class=xl82>0</TD><TD class=xl82>1</TD><TD class=xl81>4</TD><TD class=xl75>

</TD><TD class=xl71>33%</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: red; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>5</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #ffc000; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>8</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #7030a0; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl81>9</TD><TD class=xl83>

</TD><TD class=xl83>

</TD><TD class=xl83>

</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="HEIGHT: 14.4pt" class=xl76 height=19>2</TD><TD class=xl76>4</TD><TD class=xl76>5</TD><TD class=xl77>9</TD><TD class=xl77>

</TD><TD class=xl79>67%</TD><TD style="BORDER-LEFT: medium none" class=xl78>

</TD><TD class=xl76>7</TD><TD class=xl76>9</TD><TD class=xl76>0</TD><TD class=xl77>4</TD><TD class=xl80>

</TD><TD class=xl79>67%</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #ffc000; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl76>8</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #7030a0; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl76>9</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #ddd9c3; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl77>

</TD><TD class=xl83>9</TD><TD class=xl83>9</TD><TD class=xl83>

</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="HEIGHT: 14.4pt" class=xl82 height=19>2</TD><TD class=xl82>4</TD><TD class=xl82>5</TD><TD class=xl81>9</TD><TD class=xl81>

</TD><TD class=xl71>33%</TD><TD style="BORDER-LEFT: medium none" class=xl72>

</TD><TD class=xl82>7</TD><TD class=xl82>9</TD><TD class=xl82>0</TD><TD class=xl81>4</TD><TD class=xl75>

</TD><TD class=xl71>33%</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #ffc000; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>8</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #7030a0; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>9</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #ddd9c3; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl81>

</TD><TD class=xl83>9</TD><TD class=xl83>9</TD><TD class=xl83>

</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="HEIGHT: 14.4pt" class=xl82 height=19>1</TD><TD class=xl82>4</TD><TD class=xl82>5</TD><TD class=xl81>9</TD><TD class=xl81>

</TD><TD class=xl71>33%</TD><TD style="BORDER-LEFT: medium none" class=xl72>

</TD><TD class=xl82>6</TD><TD class=xl82>9</TD><TD class=xl82>0</TD><TD class=xl81>4</TD><TD class=xl75>

</TD><TD class=xl71>33%</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #ffc000; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>8</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #7030a0; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>9</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #ddd9c3; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl81>

</TD><TD class=xl83>9</TD><TD class=xl83>9</TD><TD class=xl83>

</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="HEIGHT: 14.4pt" class=xl82 height=19>1</TD><TD class=xl82>4</TD><TD class=xl82>5</TD><TD class=xl81>9</TD><TD class=xl81>

</TD><TD class=xl71>67%</TD><TD style="BORDER-LEFT: medium none" class=xl72>

</TD><TD class=xl82>6</TD><TD class=xl82>9</TD><TD class=xl82>0</TD><TD class=xl81>4</TD><TD class=xl75>

</TD><TD class=xl71>33%</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #ffc000; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>8</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #7030a0; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: medium none; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl82>9</TD><TD style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; FONT-FAMILY: Calibri; BACKGROUND: #ddd9c3; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: medium none; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl81>

</TD><TD class=xl83>9</TD><TD class=xl83>9</TD><TD class=xl83>

</TD></TR></TBODY></TABLE>
Click to expand...

Things to note...

1) The formula misses dollar signs... This explains why we have duplicates in the results area. The formula should have been:

=IFERROR(SMALL(IF(ISNUMBER(MATCH($AE2:$AG2,IF(ISNUMBER(MATCH($Y2:$AB2,$R2:$U2,0)),$Y2:$AB2,"#"),0)),$AE2:$AG2),COLUMNS($AH2:AH2)),"")

2) It appears that there can be empty cells in the data. Such cells are read as 0 if they are not excluded. This explains 0's in the result area.

3) More important: You seem to expect 4 and 9 where we now have just 9. Looks like you want results of pairwise comparisons with no duplications. If this is the case, a VBA solution might be far less inconvenient.
 
Upvote 0

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.
I guess if the only thing better than this would be a vb solution then I'll just know to watch the 0s that show up, because otherwise this is perfect. I just tried it on real data and it works. Thanks a lot for the help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
706
smozgur
smozgur
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
A
  • Question Question
exel 2002 total accumulated payments
Replies
4
Views
737
anon125
A
T
  • Question Question
VBA to launch userform by selecting a cell
2
Replies
10
Views
2K
Logit
Logit
C
  • Question Question
Sum
Replies
3
Views
1K
pbornemeier
pbornemeier

Forum statistics

Threads
1,225,156
Messages
6,183,236
Members
453,152
Latest member
ChrisMd

Share this page

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