Pull Values from List Exluding Matching Values in Second List

**sskgintl** · 07-15-2014, 09:45 PM

The basic question is: Is it possible to pull a list of values out of an array based on a couple of criteria, but exclude them if they exist with certain criteria in a second array?

To be more concrete: I have a spreadsheet tracking a fundraising campaign. Tab 1 is "All Donations" and lists every donation received. Tab 2 is "Pledges" and lists donations that have been promised, but not yet made. I am creating a third tab to pull a summary of the month's activity a committee.

I can successfully pull a list of all of the donations from the past month using this formula:

=INDEX('All Donations'!D:D,SMALL(IF('All Donations'!$C:$C>=(EOMONTH($B$3,-1)+1),IF('All Donations'!$C:$C<=$B$3,ROW('All Donations'!$A:$A))),ROWS($A$31:A31)))

I would like to exclude any donations which show on the Pledges tab as having been applied toward a pledge. This means checking 3 criteria on the Pledges tab - the name (column A), the date (J) and amount (K). If all 3 of these match the data on the All Donations tab, the record should not show on the list.

My best shot at it was adding the red if functions:

=INDEX('All Donations'!D:D,SMALL(IF('All Donations'!$C:$C>=(EOMONTH($B$3,-1)+1),IF('All Donations'!$C:$C<=$B$3,IF('All Donations'!$C:$C<>Pledges!J:J,IF('All Donations'!D:D<>Pledges!A:A,IF('All Donations'!E:E<>Pledges!K:K,ROW('All Donations'!$A:$A)))))),ROWS($A$33:A33)))

Needless to say, it didn't work. So, I'm appealing to anyone brilliant (or at least smarter than me) out there.

**azumi** · 07-15-2014, 09:52 PM

You have your small sample workbook? that desscribe the problem and enplain the expected results too, and try to upload at the forum, click "Go Advanced" button and find Paperclip button to attach your file, its difficult without seeing your sample file

Regards