How to tell if row with identical criteria exists in different spreadsheet

Can someone help me write a formula that does the following?

SS1= spreadsheet 1
SS2= spreadsheet 2
Cn= column
x=Column

If data in SS1,CnC = data in SS2,CnB

and

if data in SS1,CnD = data in SS2,CnC

and

if data in SS1,CnE = data in SS2,CnD

and

if data in SS1,CnG = data in SS2,CnH

then return "Yes" in the cell

Thank you!
Mattman123

Are the two files open at the same time?

Should column G and column H be the other way round for your final test?

Pete

The two files are open at the same time. The columns indicated in the final test are accurate as is. I can move data to a different column if it would simply the function, although that's not preferable.

No, don't move the columns - it just seemed more consistent to me if they were the other way round.

If the two files are both open then you don't need to concern yourself with the path - Excel will put it in for you. Assuming both files have an .xlsx extension, and that the data is on Sheet1 in both files, then you can do this:

=IF('[SS1.xlsx]Sheet1'!C2&'[SS1.xlsx]Sheet1'!D2&'[SS1.xlsx]Sheet1'!E2&'[SS1.xlsx]Sheet1'!G2='[SS2.xlsx]Sheet1'!B2&'[SS2.xlsx]Sheet1'!C2&'[SS2.xlsx]Sheet1'!D2&'[SS2.xlsx]Sheet1'!H2,"yes","no")

Hope this helps.

Pete

Pete,

Major complication: The rows will not correspond from SS1 to SS2 like you have there. One row could be 23 while the other is 89.

Also, data within each column repeats many times, so you can't create a unique identifier without all four fields.

Each of the conditional tests will need to be separate. Does that make sense?

Is there a way to do this with like INDEX MATCH?

Thank you!

Notice that I was concatenating the 4 values together, so they had to be identical on both sheets to generate a match.

I suggest you do the same thing in both files, so in SS1 (let's say in column X) put this formula in X2:

=C2&D2&E2&G2

and copy down to cover all your data. Then put this formula in X2 of SS2:

=B2&C2&D2&H2

and copy down. Then you could use this in Y2 (assuming the formula is in SS1 Sheet1, then we don't need to specify those file and sheet names):

=IF(ISNA(MATCH(X2,'[SS2.xlsx]Sheet1'!X:X,0)),"no","yes")

then copy this down.

Hope this helps.

Pete