Excluding certain results from an Index and Match operation

[wiscy]

Looking for advice on a formula to exclude a certain ID from the broader data on what I've labeled Sheet2. The formula that doesn't work is below, but I think it indicates what I'm trying to do. Not quite sure how to accomplish this, so any guidance would be appreciated.

=INDEX('Sheet2'!F:F,MATCH([@category]&[@[ctry]]&('Sheet2'!B:B<>[@[ID]]),'Sheet2'!C:C&'Sheet2'!D:D,0))

[#2]

I believe you want something similar to this? You may need to hit Ctrl+Shift+Enter after keying/copying it into the formula bar (not sure if the most current versions of excel still require that or not).

=INDEX('Sheet2'!F:F,MATCH(1,('Sheet2'!C:C=[@category])*('Sheet2'!D:D=[@[ctry]])*('Sheet2'!B:B<>[@[ID]]),0))

[#3]

@JMB17   Thank you; however, that didn't quite work.  I've attached a spreadsheet using the formula you suggested.  I'd like to sum all of the values drawn from sheet 2 that match the category and ctry fields but excludes amounts related to the ID from Sheet 1.  Suggestions would be appreciated. 

[#4]

@wiscy 

Here

it could be

=SUMPRODUCT(
   ($B$2:$B$71=G2)*
   ($C$2:$C$71=H2)*
   (COUNTIF(Sheet1!$A$2:$A$11,$A$2:$A$71)=0)*
   $D$2:$D$71
)

[#5 · Accepted solution]

@wiscy 

It's a little bit clearer what you're trying to do. The index/match formula in your original post is indicative of a lookup function, not a sum function.

But, it still depends on whether you want to exclude the entries where the ID, Category, and Ctry match Sheet1, or exclude all entries on Sheet2 where the ID matches any of the ID's listed on Sheet1 regardless of Category and Ctry. I believe both scenarios yield the same results, but I don't know if  that may always be true.

I attached a file with examples for both. Though the second example should yield the same results as what Sergei has already suggested.

[#6]

That was what I was looking for. Thanks!