VLOOKUP

Miles Allison

Column1 Column2 Column3

1 1/1/20 Complete

3 1/1/20 Pending

2 1/1/20 Pending

Column 4 Column5 Column6

3 1/1/20 Complete

2 1/1/20 Pending

1 1/1/20 Complete

I need a formula that can identify when one of the cells in the first three columns is different from the cells in the last three columns. In this example, 3 should be identified as the change while 1 and 2 will not be flagged. Basically, IF column 1 and 4 match, THEN search to see if column 2 and column 5 match, and if column3 and column6 match.

Nik Fuentes

Add a column for Row ID [Auto-Number], then in your check column add

=if(Index([Column1]:[Column6], [Row ID]@row, 1) = Index([Column1]:[Column6], [Row ID]@row, 4), if(Index([Column1]:[Column6], [Row ID]@row, 2) = Index([Column1]:[Column6], [Row ID]@row, 5), if(Index([Column1]:[Column6], [Row ID]@row, 3) = Index([Column1]:[Column6], [Row ID]@row, 6), "Exact Match", "No Match Position 3"), "No Match Position 2"), "No Match Position 1")

a little tedious to set up, but you can copy/paste the Index() part and just change the column index. as a bonus, this way tells you which position gives the match error first. If you want it to be a checkbox instead of a text string, make "Exact Match" a 1 and each of the "No Match Position #"s a 0. you can also hide the Row ID column, I doubt you'll need it