Find matching lines based on multiple criteria

I use a lot of match, index, and countif formulas in a variety of different ways, however I have a hard time knowing how to use these when I need to find "matches" based multiple criteria.

The attached spreadsheet shows an example of what I'm looking for. The sheet is of a list of two customers and the contracts they are on. I want to create a formula in column J for each row, that returns a result only if the customer number and contract number match, but the end dates do not for any other row of data in the list. The two blue lines in the attachment show such an instance. So I would want my formula in column J to return a result (either number or word, just something to sort by) in cells J6 and J7

The thing i have a hard time doing in my formula attempts is telling excel to compare the data in a given row against only against data in other rows as opposed to data in columns. E.G. I can return results where the customer number matches another row with the same customer and when the contract number matches, but not where the customer number and contract number are matching on the same row.

I hope I have explained this clearly. In the past I have done this task observationally but sometimes I deal with thousands of lines and anything that could speed this up would be greatly appreciated.

I'm not sure what you are asking for your dataset. Do you want J6:J7 flagged because they have the same contract, customer name, and different end dates? What makes those rows special?


As far as multi-criteria indices go, you can use: INDEX(table,MATCH(criteria2,IF(criteria range1=criteria1, criteria range2),0))

Example: INDEX(A1:A5,MATCH(D10,IF(C1:C5=E10,B1:B5),0)

If C1:C5 = E10, then Match D10 against the rows of B1:B5

Another popular variation is INDEX(table,MATCH(criteria1&criteria2,criteria range1&criteria range2,0))

Example: INDEX(A1:A5,MATCH(D10&E10,B1:B5&C1:C5,0))

These both have to be entered as an array using Ctrl+Shift+Enter.