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?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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