Forum Discussion
Excluding certain results from an Index and Match operation
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))
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.
5 Replies
- 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))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.
