match columns by multiple criterias

[Conscript]

Hello guys,
i have a lot of data and im looking for a way how to automatize it.

Im trying to make matching by multiple criteria based on this six exact match criterias (only matching criteria 1 should be in the opposite value). I need to match them only if all criterias are met. I tried some combined IFs, but i didnt manage to get into something which would help me more than vlookup.

i.e. Im trying to match 2 blue rows based on their values.

probably the most difficult problem for me is to tell the excel to skip some columns in criteria 2-5 and search for the same value as in the first column

any ideas?

 

[p45cal]

I don't really like it, but you might get away with this formula (array-entered, meaning commit it to the sheet using Ctrl+Shift+Enter, not just Enter) in any cell in row 3 of your sheet:

=IF(ISERROR(VLOOKUP(B3&C3&D3&I3&-J3,$B$3:$B$10&$C$3:$C$10&$D$3:$D$10&$I$3:$I$10&$J$3:$J$10,1,0)),"n","y")

then copy down.

or you could normally enter:

=IF(SUMPRODUCT(($B$3:$B$10=B3)*($C$3:$C$10=C3)*($D$3:$D$10=D3)*($I$3:$I$10=I3)*($J$3:$J$10=-J3))>0,"y","n")

and copy down.

 

[bosco_yip]

Maybe,

Q3, normally enter & copied down :

=IF(COUNTIFS(J$3:J$100,-J3,B$3:B$100,B3,C$3:C$100,C3,D$3:D$100,D3,I$3:I$100,I3),"y","n")

Regards
Bosco

 

[Conscript]

Hello guys,
thanks for your formulas!
I tried all of the solutions on a bigger file and i found out that p45cal's first solution works exactly as Bosco's, and I think that both of them are quite useful and i would stick to those solutions.

i had to add one more reference in order to match them more correctly.
i wonder, if there is any way, how to tell this function to match only unique values (J) and ignore duplicates in J column

=IF(COUNTIFS(J$21:J$500,-J25,B$21:B$500,B25,C$21:C$500,C25,D$21:D$500,D25,I$21:I$500,I25,M$21:M$500,M25),"Y","N")

 

[p45cal]

i wonder, if there is any way, how to tell this function to match only unique values (J) and ignore duplicates in J column

Do you mean that if there are duplicates in J leading to multiple contenders for 'y' contenders, you don't want to see any y's for them? Perhaps you only want to see one? If so, which one should get it?

 

[Conscript]

Hi p45cal,
sorry for my delayed response. I tried to analyze data with that formula i noticed that i still need to display all those contenders so i would consider this as solved.

Again, i would like to thank you for your and bosco_yips help.