Index/Match Multiple Criteria No Duplicates

M

MischMisch

New Member
Joined
Apr 22, 2014
Messages
5
I am trying to use INDEX/MATCH to locate data, but I do not want duplicate results. Could you take a look at my example below and let me know if you come up with anything.

I need to net the Doc IDs with a negative amount with Doc IDs that have a positive amount equal to or greater than the absolute value of the negative Doc ID amount. My current formula (see below) stops at the first instance and duplicates where the criteria are the same. What formula would allow me to extract the next matching Doc ID? There are thousands of lines and I cannot have duplicates.


[TABLE="class: cms_table"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]A[/TD]
[TD="width: 141, bgcolor: transparent"]B[/TD]
[TD="width: 161, bgcolor: transparent"]C[/TD]
[TD="width: 82, bgcolor: transparent"]D[/TD]
[TD="width: 103, bgcolor: transparent"]E[/TD]
[TD="width: 64, bgcolor: transparent"]F[/TD]
[TD="width: 109, bgcolor: transparent"]G[/TD]
[TD="width: 97, bgcolor: transparent"]H[/TD]
[TD="width: 64, bgcolor: transparent"]I[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Sheet One[/TD]
[TD="width: 141, bgcolor: transparent"]a&b&c&e[/TD]
[TD="width: 161, bgcolor: transparent"][/TD]
[TD="width: 82, bgcolor: transparent"]a[/TD]
[TD="width: 103, bgcolor: transparent"]b[/TD]
[TD="width: 64, bgcolor: transparent"]c[/TD]
[TD="width: 109, bgcolor: transparent"]d[/TD]
[TD="width: 97, bgcolor: transparent"]e[/TD]
[TD="width: 64, bgcolor: transparent"]f[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #D9D9D9"]Rows[/TD]
[TD="width: 141, bgcolor: #D9D9D9"]Lookup Value[/TD]
[TD="width: 161, bgcolor: #D9D9D9"]PO Number of closest match[/TD]
[TD="width: 82, bgcolor: #D9D9D9"]Company[/TD]
[TD="width: 103, bgcolor: #D9D9D9"]Location Name[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Asset Type[/TD]
[TD="width: 109, bgcolor: #D9D9D9"]Doc ID[/TD]
[TD="width: 97, bgcolor: #D9D9D9"]Deliver Date[/TD]
[TD="width: 64, bgcolor: #D9D9D9"] Amount [/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]8[/TD]
[TD="bgcolor: transparent"]255Apple39862[/TD]
[TD="width: 161, bgcolor: transparent"][TABLE="class: cms_table"]
<tbody>[TR]
[TD="width: 161, bgcolor: transparent"]{=INDEX($F$20:$F$26,MATCH(1,($B$20:$B$26=B8)*($H$20:$H$26>=-I8),0))}[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]Apple[/TD]
[TD="bgcolor: transparent"]28816[/TD]
[TD="bgcolor: transparent, align: right"]2/18/2009[/TD]
[TD="bgcolor: transparent"] (42)[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]9[/TD]
[TD="bgcolor: transparent"]255Apple39862[/TD]
[TD="bgcolor: transparent, align: right"]30088[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]Apple[/TD]
[TD="bgcolor: transparent"]35243[/TD]
[TD="bgcolor: transparent, align: right"]2/18/2009[/TD]
[TD="bgcolor: transparent"] (2,400)[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]255Orange39862[/TD]
[TD="bgcolor: transparent, align: right"]34521[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]Orange[/TD]
[TD="bgcolor: transparent"]37532[/TD]
[TD="bgcolor: transparent, align: right"]2/18/2009[/TD]
[TD="bgcolor: transparent"] (50)[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]11[/TD]
[TD="bgcolor: transparent"]2510Apple41622[/TD]
[TD="bgcolor: transparent, align: right"]28814[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]Apple[/TD]
[TD="bgcolor: transparent"]97573[/TD]
[TD="bgcolor: transparent, align: right"]12/14/2013[/TD]
[TD="bgcolor: transparent"] (350)[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]12[/TD]
[TD="bgcolor: transparent"]2513Apple41474[/TD]
[TD="bgcolor: yellow, align: right"]30500[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"]Apple[/TD]
[TD="bgcolor: transparent"]93255[/TD]
[TD="bgcolor: transparent, align: right"]7/19/2013[/TD]
[TD="bgcolor: transparent"] (2,000)[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"]2513Apple41474[/TD]
[TD="bgcolor: yellow, align: right"]30500[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"]Apple[/TD]
[TD="bgcolor: transparent"]95549[/TD]
[TD="bgcolor: transparent, align: right"]7/19/2013[/TD]
[TD="bgcolor: transparent"] (2,000)[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]14[/TD]
[TD="bgcolor: transparent"]2513Banana41474[/TD]
[TD="bgcolor: transparent, align: right"]31850[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"]Banana[/TD]
[TD="bgcolor: transparent"]94769[/TD]
[TD="bgcolor: transparent, align: right"]7/19/2013[/TD]
[TD="bgcolor: transparent"] (550)[/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 141, bgcolor: transparent"][/TD]
[TD="width: 161, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 109, bgcolor: transparent"][/TD]
[TD="width: 97, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 141, bgcolor: transparent"][/TD]
[TD="width: 161, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 109, bgcolor: transparent"][/TD]
[TD="width: 97, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]Sheet Two[/TD]
[TD="width: 141, bgcolor: transparent"]a&b&c&e[/TD]
[TD="width: 161, bgcolor: transparent"]a[/TD]
[TD="width: 82, bgcolor: transparent"]b[/TD]
[TD="width: 103, bgcolor: transparent"]c[/TD]
[TD="width: 64, bgcolor: transparent"]d[/TD]
[TD="width: 109, bgcolor: transparent"]e[/TD]
[TD="width: 97, bgcolor: transparent"]f[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: #D9D9D9"]Rows[/TD]
[TD="width: 141, bgcolor: #D9D9D9"]Lookup Value[/TD]
[TD="width: 161, bgcolor: #D9D9D9"]Company[/TD]
[TD="width: 82, bgcolor: #D9D9D9"]Location Name[/TD]
[TD="width: 103, bgcolor: #D9D9D9"]Asset Type[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]Doc ID[/TD]
[TD="width: 109, bgcolor: #D9D9D9"]Deliver Date[/TD]
[TD="width: 97, bgcolor: #D9D9D9"] Amount [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]20[/TD]
[TD="bgcolor: transparent"]255Apple39862[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]Apple[/TD]
[TD="bgcolor: transparent"]35321[/TD]
[TD="bgcolor: transparent, align: right"]2/18/2009[/TD]
[TD="bgcolor: transparent"] 60 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]21[/TD]
[TD="bgcolor: transparent"]255Apple39862[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]Apple[/TD]
[TD="bgcolor: transparent"]30088[/TD]
[TD="bgcolor: transparent, align: right"]2/18/2009[/TD]
[TD="bgcolor: transparent"] 2,500 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]22[/TD]
[TD="bgcolor: transparent"]255Orange39862[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]Orange[/TD]
[TD="bgcolor: transparent"]34521[/TD]
[TD="bgcolor: transparent, align: right"]2/18/2009[/TD]
[TD="bgcolor: transparent"] 60 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]23[/TD]
[TD="bgcolor: transparent"]2510Apple41622[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]Apple[/TD]
[TD="bgcolor: transparent"]28814[/TD]
[TD="bgcolor: transparent, align: right"]12/14/2013[/TD]
[TD="bgcolor: transparent"] 700 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]24[/TD]
[TD="bgcolor: transparent"]2513Apple41474[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"]Apple[/TD]
[TD="bgcolor: transparent"]30500[/TD]
[TD="bgcolor: transparent, align: right"]7/19/2013[/TD]
[TD="bgcolor: transparent"] 2,000 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]2513Apple41474[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"]Apple[/TD]
[TD="bgcolor: transparent"]32496[/TD]
[TD="bgcolor: transparent, align: right"]7/19/2013[/TD]
[TD="bgcolor: transparent"] 2,000 [/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent"]26[/TD]
[TD="bgcolor: transparent"]2513Banana41474[/TD]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent"]Banana[/TD]
[TD="bgcolor: transparent"]31850[/TD]
[TD="bgcolor: transparent, align: right"]7/19/2013[/TD]
[TD="bgcolor: transparent"] 600
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
You must log in or register to reply here.

Similar threads

ExcelNewbie2020
  • Solved
Lookup duplicate value with multiple criteria
Replies
9
Views
234
Fluff
Fluff
M
  • Question Question
Index Match function List based on date
Replies
7
Views
243
Fluff
Fluff
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
338
Asbestos_Jen
Asbestos_Jen
I
  • Solved
Look for Cell Data in Cell Array, and Cell Data in Row Array and if match show this Cell Data
Replies
4
Views
237
Iccreamann21
I
T
  • Question Question
Multiple Search Criteria in Excel Table
Replies
4
Views
123
KOKOSEK
KOKOSEK

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

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