Compare list to a table, and output multiple reference numbers

C

Cipher226

New Member
Joined
Sep 9, 2011
Messages
16
I'm trying to check a list of numbers to a table and output a reference number of sorts. Now, I've got the formula to do this with some help. Now, the issue is, the table has over 600 entries. There are multiple reference numbers that have the same combination of numbers. I would like to be able to display all the different reference numbers. Below is what I've got.

The formula I'm using to get the reference numbers is

=INDEX(Sheet3!$A$4:$A$626,MATCH(B2&C2&D2&E2&F2&G2&H2&I2&J2&K2,Sheet3!C4:C626&Sheet3!D4:D626&Sheet3!E4:E626&Sheet3!F4:F626&Sheet3!G4:G626&Sheet3!H4:H626&Sheet3!I4:I626&Sheet3!J4:J626&Sheet3!K4:K626&Sheet3!L4:L626,0))

Excel 2000<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Group</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">01</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">10</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">11</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">14</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">16-1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">16-2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">17</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">21</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">37</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">40</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">503</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">509</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">504</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">544</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">540</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">507</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">514</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">501</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">500</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>Assembly Number</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">AT 540</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836424</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>
Sheet1​



Excel 2000<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Main Housing</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Oil Pump</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Speed & Governor Drive Gears</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Clutches / Gear Unit</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Control Valve</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Retarder Control valve</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Governor</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Torque Converter</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Oil Pan</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Vacum Modulator</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Assembly Number</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Model</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">01</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">11</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">14</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">16-1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">16-2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">17</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">21</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">37</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">40</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">6835200</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">AT 540</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">503</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">509</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">504</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">544</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">540</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">507</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">514</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">501</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">6835200</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836424</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">540</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836424</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836425</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">519</TD><TD style="TEXT-ALIGN: center">540</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836425</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836426</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">519</TD><TD style="TEXT-ALIGN: center">539</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836426</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836427</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">541</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836427</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836428</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">519</TD><TD style="TEXT-ALIGN: center">541</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836428</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836431</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">539</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836431</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837520</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">510</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">542</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837520</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837533</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">510</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">543</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837533</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837535</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">510</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">508</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837535</TD></TR></TBODY></TABLE>
Sheet3​




Any help would be greatly appreciated.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Maybe this (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Group</td><td style="text-align: center;;">1</td><td style="text-align: center;;">10</td><td style="text-align: center;;">11</td><td style="text-align: center;;">14</td><td style="text-align: center;;">16-1</td><td style="text-align: center;;">16-2</td><td style="text-align: center;;">17</td><td style="text-align: center;;">21</td><td style="text-align: center;;">37</td><td style="text-align: center;;">40</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;">503</td><td style="text-align: center;;">509</td><td style="text-align: center;;">504</td><td style="text-align: center;;">544</td><td style="text-align: center;;">540</td><td style="text-align: center;;"></td><td style="text-align: center;;">507</td><td style="text-align: center;;">514</td><td style="text-align: center;;">501</td><td style="text-align: center;;">500</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">Assembly Number</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">AT 540</td><td style="text-align: center;;">6835200</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;">6836424</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td><td style="text-align: center;;">******</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">{=IF(<font color="Blue">SUM(<font color="Red">--(<font color="Green">MMULT(<font color="Purple">--(<font color="Teal">B$2:K$2=Sheet3!C$3:L$12</font>),{1;1;1;1;1;1;1;1;1;1}</font>)=10</font>)</font>)>=ROWS(<font color="Red">B$6:B6</font>),
INDEX(<font color="Red">Sheet3!A$3:A$12,SMALL(<font color="Green">IF(<font color="Purple">MMULT(<font color="Teal">--(<font color="#FF00FF">B$2:K$2=Sheet3!C$3:L$12</font>),{1;1;1;1;1;1;1;1;1;1}</font>)=10,ROW(<font color="Teal">Sheet3!A$3:A$12</font>)-ROW(<font color="Teal">Sheet3!A$3</font>)+1</font>),ROWS(<font color="Purple">B$6:B6</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz
 
Upvote 0
I copied the formula, ctrl+shift+enter, even tried just enter, but all I get is a blank cell. No error or anything, just blank. Does it make a difference that I'm using only excel 2000?
 
Upvote 0
Cipher226 said:
I'm trying to check a list of numbers to a table and output a reference number of sorts. Now, I've got the formula to do this with some help. Now, the issue is, the table has over 600 entries. There are multiple reference numbers that have the same combination of numbers. I would like to be able to display all the different reference numbers. Below is what I've got.

The formula I'm using to get the reference numbers is

=INDEX(Sheet3!$A$4:$A$626,MATCH(B2&C2&D2&E2&F2&G2&H2&I2&J2&K2,Sheet3!C4:C626&Sheet3!D4:D626&Sheet3!E4:E626&Sheet3!F4:F626&Sheet3!G4:G626&Sheet3!H4:H626&Sheet3!I4:I626&Sheet3!J4:J626&Sheet3!K4:K626&Sheet3!L4:L626,0))

Excel 2000<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">Group</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">01</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">10</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">11</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">14</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">16-1</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">16-2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">17</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">21</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">37</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; TEXT-ALIGN: center">40</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #000000; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">503</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">509</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">504</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">544</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">540</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">507</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">514</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">501</TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">500</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-TOP: black 1px solid; TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>Assembly Number</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">AT 540</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836424</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>
Sheet1​



Excel 2000<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Main Housing</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Oil Pump</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Speed & Governor Drive Gears</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Clutches / Gear Unit</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Control Valve</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Retarder Control valve</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Governor</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Torque Converter</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Oil Pan</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Vacum Modulator</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Assembly Number</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Model</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">01</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">11</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">14</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">16-1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">16-2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">17</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">21</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">37</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">40</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">6835200</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">AT 540</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">503</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">509</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">504</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">544</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">540</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center"></TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">507</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">514</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">501</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: center">6835200</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836424</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">540</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836424</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836425</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">519</TD><TD style="TEXT-ALIGN: center">540</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836425</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836426</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">519</TD><TD style="TEXT-ALIGN: center">539</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836426</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836427</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">541</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836427</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836428</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">519</TD><TD style="TEXT-ALIGN: center">541</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836428</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836431</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">509</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">539</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">506</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">500</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836431</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">10</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837520</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">510</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">542</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837520</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">11</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837533</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">510</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">543</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">507</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837533</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">12</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837535</TD><TD style="TEXT-ALIGN: center">AT 540</TD><TD style="TEXT-ALIGN: center">503</TD><TD style="TEXT-ALIGN: center">510</TD><TD style="TEXT-ALIGN: center">504</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center">544</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">508</TD><TD style="TEXT-ALIGN: center">514</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="TEXT-ALIGN: center">501</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6837535</TD></TR></TBODY></TABLE>
Sheet3​




Any help would be greatly appreciated.
Click to expand...
I assume there will not be any duplicate numbers on a row?
 
Upvote 0
I know there is the chance that one Assembly Number (Reference number) can have duplicate numbers, although it's over 600 lines, so I don't know for sure. I just know that it is possible.
 
Upvote 0
Cipher226 said:
I copied the formula, ctrl+shift+enter, even tried just enter, but all I get is a blank cell. No error or anything, just blank. Does it make a difference that I'm using only excel 2000?
Click to expand...

First:

You have to type the formula below and then press ctrl+shift+enter.

=IF(SUM(--(MMULT(--(B$2:K$2=Sheet3!C$3:L$12),{1;1;1;1;1;1;1;1;1;1})=10))>=ROWS(B$6:B6),INDEX(Sheet3!A$3:A$12,SMALL(IF(MMULT(--(B$2:K$2=Sheet3!C$3:L$12),{1;1;1;1;1;1;1;1;1;1})=10,ROW(Sheet3!A$3:A$12)-ROW(Sheet3!A$3)+1),ROWS(B$6:B6))),"")

Second:

I did some tests with Excel 2000 and it worked.

Markmzz
 
Upvote 0
Cipher226 said:
I know there is the chance that one Assembly Number (Reference number) can have duplicate numbers, although it's over 600 lines, so I don't know for sure. I just know that it is possible.
Click to expand...
Try this...

Create this defined name:
  • Goto Insert>Name>Define
  • Name: Array
  • Refers to: ={1;1;1;1;1;1;1;1;1;1}
  • OK out
Enter this array formula** in B5:

=INDEX(Sheet3!B3:B12,MATCH(TRUE,MMULT(--(B2:K2=Sheet3!C3:L12),Array)=10,0))

Enter this array formula** in B6:

=INDEX(Sheet3!A3:A12,MATCH(TRUE,MMULT(--(B2:K2=Sheet3!C3:L12),Array)=10,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
A small modification:

=IF(SUM(--(MMULT(--(B$2:K$2=Sheet3!C$3:L$626),LIN($1:$10)^0)=10))>=ROWS(B$6:B6),
INDEX(Sheet3!A$3:A$626,SMALL(IF(MMULT(--(B$2:K$2=Sheet3!C$3:L$626),LIN($1:$10)^0)=10,ROW(Sheet3!A$3:A$626)-ROW(Sheet3!A$3)+1),ROWS(B$6:B6))),"")

Markmzz
 
Upvote 0
I've got to be doing something wrong here... T.Valko, your formula shows up as #n/a, and Markmzz, your formula shows up as #name?. I typed both arrays, and copied both arrays. Truth be told, I don't understand either formula so I can't even begin to poke around and see what I did wrong.

Below is a link to my actual excel sheet without either formula entered as they do not want to work for me. It's not the greatest file sharing site, gotta click the left button for free download and wait about a minute before you can download the file. But any assistance would be much appreciated.

AT545 Assembly Number List.xls - 123.5 KB
 
Upvote 0
Cipher226 said:
I've got to be doing something wrong here... T.Valko, your formula shows up as #n/a, and Markmzz, your formula shows up as #name?. I typed both arrays, and copied both arrays. Truth be told, I don't understand either formula so I can't even begin to poke around and see what I did wrong.

Below is a link to my actual excel sheet without either formula entered as they do not want to work for me. It's not the greatest file sharing site, gotta click the left button for free download and wait about a minute before you can download the file. But any assistance would be much appreciated.

AT545 Assembly Number List.xls - 123.5 KB
Click to expand...
You had/have a formatting issue.

On Sheet1 and the Data sheet the group code numbers were formatted as TEXT but on Sheet3 the group codes were formatted as GENERAL.

So, there is/was a data type mismatch causing the formulas to fail.

I fixed the data and now the formulas work as expected.

Here's your updated file:

AT545_Assembly_Number_List(1).xls

http://cjoint.com/?AItsmjQRbAb
 
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
616
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
E
  • Question Question
Adding a custom message.
Replies
1
Views
659
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,509
Messages
6,179,194
Members
452,893
Latest member
denay

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