Search letters and numbers to see if exist in another column by at least 7 characters

Dear community,

I am trying to create a formula to use in Excel 2010.
The formula should try to find a match searching in 2 columns of at least 7 characters (letters and numbers) and return "match" as a result if found.

My issue:
I have two columns, Pass1 and Pass2. containing characters mixed with letters and numbers. I need to know if the characters are found in both columns.
In column Pass1 the order is correct and in column Pass2 the order may be incorrect but I still need it to return "match" if it has the same characters, at least 7 should be enough. It also need to disregard of capital and small letters.

What I have tried:
I have used ChatGPT and Excel AI and got this formula " =IF(SUMPRODUCT(--(EXACT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1))))>=7,"Match","No Match") "

But it just return with errors when put into C2.

Attached file:
The file contains columns Pass1 with correct order and Pass2 with incorrect order, but in the file all are matched by 7 characters so in column C it should all return "Match".

Thank you in advance!
Mary

1. All your strings were 10 characters long. Are they, in reality?

2. A "No Match" or two should have been in your sample.

3. try:

=IF(A3="","",IF(SUM(--(ISNUMBER(MATCH(MID(A3,SEQUENCE(10),1),MID(B3,SEQUENCE(10),1),0))))>=7,"Match","No match"))

Pages is not an Excel version, and it’s not even a spreadsheet package. Are you using Apple Sheets?

Hi Glenn and thank you for helping me out.

I am now trying in Excel 2010. To answer your questions
1. No, they can vary in length as they are passport numbers from different countries
2. I will add now, sorry for that
3. I did try this in column C2 but it gives "No match" in all thew columns.

Do you have any other ideas of what I do wrong since you obviously got the correct result?

Many thanks!

SEQUENCE(10) does not work in Excel 2010.

Please replace SEQUENCE(10) with ROW($1:$10) and everything works fine in Excel 2010.

This is an array formula, so you need to confirm the formula with Ctrl+Shift+Enter

Your profile stated O365, so that's what I went with! That said, Hans' suggestion will fix it.

You're welcome. Thanks for letting us know that you got an answer.

---

Please take a moment and consider clicking the "Add Reputation" button at the foot of any of the posts of anyone who helped you reach a solution here today.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

Thank you Glenn and Hans, I was not aware the difference between the two excel version, apologize for that. I will learn a lot of my very first posted issue here.

I am very newbie to create formulas and I still do not get it to work, changing the SEQUENCE(10) with ROW($1:$10) and doing the command afterwards.

It still gives me No match as a result.

If I type ROW($1:$100) it will take 100 rows I assume also, as I do not know how many rows I will have yet.
Is it also taking into account the length may vary, it will not be as the example always using just 10 characters? They are also not on the same row, the matching characters can be anywhere in the columns.

Thank you!

SEQUENCE(10) and ROW($1:$10) in the function MID refers to the individual characters.

If the ids are not always 10 characters, you need a formula that takes a variable length into account.

Please try in D2 and copy down:

Formula:

Please Login or Register  to view this content.

Confirm the formula with Ctrl+Shift+Enter

Hopefully, Hans' formula will do what you want. If not... and DEFINITELY for next time, please take care to produce a REPRESENTATIVE sample sheet that reflects what you have (different length strings, different rows, matches and no matches, etc, etc).

that will make it ieaser for us to understand your problem and help you.

Please amend your profile to show your current Excel product. "mac- pages" means nothing.

I am now trying in Excel 2010.

You said you were using Numbers and now Excel 2010 - which is it? Whichever it is needs to be in your forum profile.

You are Welcome! Thanks for the feedback and rep. Glad to have helped .