Duplicates with Index and Match

sellersd

New Member
Joined
Mar 22, 2011
Messages
2
I am needing to use a formula to return data from a spreadsheet that I have already created a constant from. I have tried the following and it works fine, but if there are multiple of the data I am comparing, it will put the first one it comes too.

=INDEX(Sheet2!A2:E100,MATCH(B2,Sheet2!B2:B100,0),1)

B2 occurs multiple times because it is a item number like ACT-0098-01. The reason it occurs multiple time is because there are different version numbers. Problem is on new spreadsheet, two column that were separate on mine are joined so I can do multiple variables.

Is there anything that I can add in the formula to reference back to the column I am creating it in and if a duplicate is found, go to the next available in the Match portion of the formula?

EX. Data (Sheet 2 - My Original)

<table width="404" border="0" cellpadding="0" cellspacing="0"><col style="width: 54pt;" width="72"> <col style="width: 62pt;" width="83" span="4"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 54pt;" width="72" height="20">ID</td> <td class="xl63" style="width: 62pt;" width="83">Material</td> <td class="xl63" style="width: 62pt;" width="83">Version</td> <td class="xl63" style="width: 62pt;" width="83">Code</td> <td class="xl63" style="width: 62pt;" width="83">Name</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">1</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-506</td> <td class="xl64">CNNA Aus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">2</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-507</td> <td class="xl64">CNNA Bus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">3</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-508</td> <td class="xl64">CNNA Cus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">4</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-509</td> <td class="xl64">CNNA Dus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">5</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-510</td> <td class="xl64">CNNA Eus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">
</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-523</td> <td class="xl64">CNNA Rus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">6</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-511</td> <td class="xl64">CNNA Fus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">
</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-524</td> <td class="xl64">CNNA Sus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">7</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-512</td> <td class="xl64">CNNA Gus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">8</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-513</td> <td class="xl64">CNNA Hus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">9</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-514</td> <td class="xl64">CNNA Ius</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">10</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-515</td> <td class="xl64">CNNA Jus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">11</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-516</td> <td class="xl64">CNNA Kus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">12</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-517</td> <td class="xl64">CNNA Lus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">13</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-518</td> <td class="xl64">CNNA Mus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">14</td> <td class="xl64">CDs</td> <td class="xl65">4.00</td> <td class="xl64">A356-519</td> <td class="xl64">CNNA Nus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">15</td> <td class="xl64">Datacard</td> <td class="xl64">12.00 v</td> <td class="xl64">A356-520</td> <td class="xl64">CNNA Ous</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">
</td> <td class="xl64">Datacard</td> <td class="xl64">12.00 v</td> <td class="xl64">A356-525</td> <td class="xl64">CNNA Tus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">
</td> <td class="xl64">Datacard</td> <td class="xl64">12.00 v</td> <td class="xl64">A356-521</td> <td class="xl64">CNNA Pus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">
</td> <td class="xl64">Datacard</td> <td class="xl64">12.00 v</td> <td class="xl64">A356-522</td> <td class="xl64">CNNA Qus</td> </tr> </tbody></table>


Result from current formula ( Sheet 1 - New data I want to add the ID Field)

<table width="443" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 129pt;" width="172"> <col style="width: 87pt;" width="116"> <col style="width: 68pt;" width="91"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt; width: 48pt;" width="64" height="20">ID</td> <td class="xl65" style="width: 129pt;" width="172">Material/Version</td> <td class="xl65" style="width: 87pt;" width="116">Code</td> <td class="xl65" style="width: 68pt;" width="91">Name</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">1</td> <td class="xl66">CDs 4.00</td> <td class="xl66">A356-506</td> <td class="xl66">CNNA Aus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">2</td> <td class="xl66">CDs 4.00</td> <td class="xl66">A356-507</td> <td class="xl66">CNNA Bus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">1</td> <td class="xl66">CDs 4.50</td> <td class="xl66">A356-506</td> <td class="xl66">CNNA Cus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">4</td> <td class="xl66">CDs 4.00</td> <td class="xl66">A356-509</td> <td class="xl66">CNNA Dus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">5</td> <td class="xl66">CDs 4.00</td> <td class="xl66">A356-510</td> <td class="xl66">CNNA Eus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">1</td> <td class="xl66">CDs 5.00</td> <td class="xl66">A356-506</td> <td class="xl66">CNNA Rus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">6</td> <td class="xl66">CDs 4.00</td> <td class="xl66">A356-511</td> <td class="xl66">CNNA Fus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">0</td> <td class="xl66">CDs 4.00</td> <td class="xl66">A356-524</td> <td class="xl66">CNNA Sus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">7</td> <td class="xl66">CDs 4.00</td> <td class="xl66">A356-512</td> <td class="xl66">CNNA Gus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">8</td> <td class="xl66">CDs 4.00</td> <td class="xl66">A356-513</td> <td class="xl66">CNNA Hus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">9</td> <td class="xl66">CDs 4.00</td> <td class="xl66">A356-514</td> <td class="xl66">CNNA Ius</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">10</td> <td class="xl66">CDs 4.00</td> <td class="xl66">A356-515</td> <td class="xl66">CNNA Jus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">11</td> <td class="xl66">CDs 4.00</td> <td class="xl66">A356-516</td> <td class="xl66">CNNA Kus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">12</td> <td class="xl66">CDs 4.00</td> <td class="xl66">A356-517</td> <td class="xl66">CNNA Lus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">13</td> <td class="xl66">CDs 4.00</td> <td class="xl66">A356-518</td> <td class="xl66">CNNA Mus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">14</td> <td class="xl66">CDs 4.00</td> <td class="xl66">A356-519</td> <td class="xl66">CNNA Nus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">15</td> <td class="xl66">Datacard 12.00 v</td> <td class="xl66">A356-520</td> <td class="xl66">CNNA Ous</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">0</td> <td class="xl66">Datacard 12.00 v</td> <td class="xl66">A356-525</td> <td class="xl66">CNNA Tus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">0</td> <td class="xl66">Datacard 12.00 v</td> <td class="xl66">A356-521</td> <td class="xl66">CNNA Pus</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl66" style="height: 15pt;" height="20">0</td> <td class="xl66">Datacard 12.00 v</td> <td class="xl66">A356-522</td> <td class="xl66">CNNA Qus</td> </tr> </tbody></table>

Any ideas?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,965
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top