Compare 2 colmns based off data in other columns

[Steve C]

i have a list of deduction codes from 2 different systems on the same
spreadsheet. i'm trying to compare the deduction codes from both lists to
see which employees had the deduction come out. i know how to use
=isna(match( to compare the deduction codes, but how can i compare the
deduction codes based off of employee number?

A B C D
EE #1 Ded Code1 EE #2 Ded Code2
111 MED 111 MED
222 MED 333 DEN
111 DEN 222 RET
333 RET 333 RET

 

[Max]

.. compare the deduction codes from both lists to

see which employees had the deduction come out.

Based on your sample data posted, take us through an example or 2 of how the
above is done

---

 

[Steve C]

I tried this formula but it doesn't work. Row 3 should return True because
EE#222 doesn't have the med ded code on side 2

=IF(a2=c2:c5,ISNA(MATCH(b2,$d$2:$d$5,false)))

Side 1 Side 2
A B C D
EE #1 Ded Code1 EE #2 Ded Code2
111 MED 111 MED
222 MED 333 DEN
111 DEN 222 RET
333 RET 333 RET

 

[Max]

Try this in E2:
=IF(B2<>"MED","",IF(AND(B2="MED",INDEX(D,MATCH(A2,C:C,0))="MED"),"Med
Deduction Ok","Check"))
Copy E2 down to the last row of data in cols A and B. Lines flagged "Check"
will be where the employee had a "MED" in col B w/o a corresponding "MED" in
col D.

 

[Steve C]

This is the right idea, but it doesn't work because the deduction code
changes when i copy it down. There are thousands of Employees and over 100
different deduction codes.

 

[Max]

Ok, think I see the intent better now. This should work for you.

In E2, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(ISNA(MATCH(1,($C$2:$C$1000=A2)*($D$2:$D$1000=B2),0)),"Check","")
Copy E2 down. Adapt the ranges to suit.

 

[Steve C]

Perfect. Much Thnx!

Ok, think I see the intent better now. This should work for you.

In E2, array-enter the formula by pressing CTRL+SHIFT+ENTER:
=IF(ISNA(MATCH(1,($C$2:$C$1000=A2)*($D$2:$D$1000=B2),0)),"Check","")
Copy E2 down. Adapt the ranges to suit.

 

[Max]

welcome, Steve.