How to decrypt multiple columns?

Match four columns

• I have four columns A,B,C,D. Columns A and B are paired together (registry # and date, respectively)and the same goes for columns C and D. What I need to know is if there exists a pair of A and B the is also in columns C and D. For example A B C D 234 21/06/1978 345 14/05/2009 123 13/03/2000 234 21/06/1978 345 14/05/2009 678 12/09/1965 567 04/06/1987 146 13/07/1987 I need to know if 234 21/06/1978 is also in columns C and D. It sort of the match function for more than 2 columns

• Answer:

  Assume your data starts in cell A1, so A1 is 234, B1 is 21/6/1978, etc etc. In cell E1, enter =IFERROR(MATCH(A1&"_"&B1,F:F,0),"") In cell F1, enter =C1&"_"&D1 then copy E1:F1 down through E1:F4, or however far down your data extends. (If you don't want the user to see col F, you can hide the col, or white-font the data) You'll see that in E1 and E2, there are blanks. This means that for rows 1 & 2, the data in cols A & B aren't matched in cols C & D. In E3, you'll see a 1. This means that the data in A3:B3 is matched by the FIRST row of data in cols C & D.