In a spreadsheet I might have the following rows:
A, boat, car
B, horse, car
C, car, boat
D, boat, car
I need a method to discover that rows A and D have their 2nd and 3rd columns matching exactly.
Row C is NOT a match because the data is not in the same sequence.
Just use the COUNTIFS() function - write something like
=IF(COUNTIFS($B$1:$B$1000;B1;$C$1:$C$1000;C1)-1;"X";"")
in the adjacent cell, stretch down to the end of the data and get the result.
Thanks! Excellent and fast answer.
The only way I could think of was to concatenate/join the data into an extra column.
Do you mean something like {=IF(COUNTIF($B$1:$B$1000&CHAR(9)&$C$1:$C$1000;$B$1:$B$1000&CHAR(9)&$C$1:$C$1000)-1;"X";"")}? Yes, this will also work, but it will eat a lot more computer resources.