Comparing to a table and outputing a number

[Cipher226]

Quick idea of what I'm looking for. We work on transmissions at our shop. The transmissions each have an Assembly Number. A reference number so to speak of that describes what parts make up the transmission. The assembly number describes at list of Groups. There are 10 groups. Not sure if that was confusing or not... but here's what I'd like to do.

I want to be able to input a number for a group as shown in the yellow below. I want the numbers I input to be compared to a table and the give me the corresponding Assembly Number for that group. What I've got so far is this.

Edit: The list is far from complete. So it will be a bit longer then what is shown.

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">741</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">615</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">524</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">645</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"></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">508</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">629</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">561</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">502</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="BORDER-BOTTOM: black 1px solid; 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="BORDER-RIGHT: black 1px solid; TEXT-ALIGN: right"></TD><TD style="BORDER-RIGHT: black 1px solid; BORDER-TOP: black 1px solid; BORDER-LEFT: black 1px solid; BORDER-BOTTOM: black 1px solid">Answer</TD><TD style="BORDER-LEFT: black 1px solid; 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></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</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></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">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></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">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></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">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></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">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">5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">6836425</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></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">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></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">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></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">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></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">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></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">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></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">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></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">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></TR></TBODY></TABLE>

Sheet3​

 

[Trouttrap2]

I notice you don't have column B in your data table displayed, therefore this formula does not use that column.

=INDEX(Sheet3!$A$4:$A$13,MATCH(B3&C3&D3&E3&F3&G3&H3&I3&J3&K3,Sheet3!C4:C13&Sheet3!D4:D13&Sheet3!E4:E13&Sheet3!F4:F13&Sheet3!G4:G13&Sheet3!H4:H13&Sheet3!I4:I13&Sheet3!J4:J13&Sheet3!K4:K13&Sheet3!L4:L13,0))

This must be entered by simultaneously hitting the Ctrl+Shift+Enter keys. Entered correctly you will {} surounding the formula in the formula bar.

 

[Cipher226]

Awesome, thank you. Works great. Now to finish the table of data, which may take me a little while and I see how the formula works. Minor tweaking, and beautiful.