Sheet1 is my main sheet I’m working off of with a bunch of data including File Numbers and Account Numbers, Sheet2 is my reference sheet with a report of all the data of files in a certain status that I want to compare to sheet 1

What I want to do is compare both sheets to find duplicates in a certain account status

I have been using an IF(ISERROR(MATCH formula to match up file numbers so Sheet1 will tell me all the duplicates by file number that are on Sheet2. Example of formula:

=IF(ISERROR(MATCH(A1,Sheet2!A:A,0)),””,”Duplicate”)

But the problem is, some files have more than one account, for example, one account is in the status I want to be listed as duplicate, the other is not on the reference sheet but has the same file number

This causes the formula to list both accounts as Duplicate since I told the formula to check for a file number.

I could just switch the formula to match the account number, but to complicate it further, the accounts on Sheet1 and Sheet2 sometime do not match completely.

For example, Sheet1 may the full account number, while sheet2 may only have a partial, say 4-12 digits

Is there a way I can use the formula to match for the account number instead, but instead of an exact match, it will match the 16 digit account number on Sheet1, with the potential partials on Sheet2?

or is there a better formula or way to go about th

I know the 0 in my formula tells Excel to look for an exact match, but the other variables only tell it to look for data greater than or less than, but I don’t think that will work in my search.

Thank you!