Finding switched matches

Working with this list of names, for example:

  1. John, Smith
  2. Jane, Doe
  3. John, Doe
  4. Smith, Joe
  5. Smith, John
  6. Doe, Jane
  7. Smith, Smith
  8. Doe, Jane
  9. Doe, Doe
  10. Jane, John

How would I find all the cases where there is a match, but switched? Returning (or marking): 1, 2, 5, 6, 8

Possible parts of the solution I’m considering:

=MATCH(A2,B:B,0) returns 6 (where “Jane” in first column matches “Jane” in 2nd column).

Also trying out nested IF statements.

Based on Opaque’s chart below, this is the desired outcome:
image description

Working with this list of names,

Your list is unclear (at least to me). Is it organized like A (3 columns),B (2 columns) or C (1 column)

image description

Either is fine, but A

Which would change my possible part I’ve been trying to use to =MATCH(B2,C:C,0)

(Also it is not too hard to switch between your C or B to A using Text to Column in the Data menu)

Either is fine, but A

I did ask information about how it is - but this response makes me assume the design is still to do.

I actually have the data in both B and A.


one(!) solution may be in the following sample file:


Please note
This solution completely ignores the case where value in column B and C of a row are identical (as per your desired outcome statement). In other words: The solution does not find duplicates of Doe,Doe or Smith,Smith in the same row even if they appear twice (or more often).

Great, I’m checking it with a larger list of data.

It works well, except for if there is a blank in one of the two columns. Those get a check.

Also it takes lots of memory. I only got it to work with a few 100 as it has crashed with my 10,000 size list.

To save some memory, I switched it to just this:


Then I can later delete the "#N/A"s

Why does =MATCH(A2&B2,B:B&A:A,0) not work in Excel?

Why does =MATCH(A2&B2,B:B&A:A,0) not work in Excel?

Ask in a Excel forum - this site is about LibreOffice (and I got no Excel since decades)