# 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.

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

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)

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.

Hello,

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

FindReverseNames.ods

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:

=MATCH(A2&B2,B:B&A:A,0)

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)