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