Duplicates with Index and Match

S

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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
623
smozgur
smozgur
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
J
  • Question Question
Creating a dynamic descending list with duplicate values
Replies
5
Views
2K
Fluff
Fluff

Forum statistics

Threads
1,224,590
Messages
6,179,756
Members
452,940
Latest member
rootytrip

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