Lookup/Match - Compare 1 list to 2 other lists based on 2 columns - example provided

If I understand you correctly... and I'm fairly certain I do... there's not enough information in your example to determine whether an expired was replaced. (I know you indicated them with notes... but I'm assuming the actual file don't have such notes. )

I only put that there to hopefully make it clear what I mean, I was trying to make a formula to do it but struggling with it.

I realize that... but what I'm saying other than your notes, there is nothing to relate 122256|8 on any worksheet to 122226|12 on the Renewed worksheet. It is possible to write a formula that checks whether there is a Renewed that don't match any Due or Expired because there is no 122226|12 on either. However, if there are two or more such instances, there's no way to determine which matches which.

I see what you are saying and this would be a problem in how I am intending to apply this. There will be multiple instances of 2 or more contracts being replaced to a new line under the same client reference.

I have added another column of data that will be unique to each particular contract (I called it 'Serial No.' on the example), so when replaced to a new line, the data in this new column will remain the same.

I am now continuing to try and figure out formulas that will correctly place a "Y" in one of the 3 columns for each line on the dues sheet.

The logic of it as I see it is that: If one appears on both the renewed and expired sheet then it has been replaced.

Any further help is much appreciated

With new attachment....

Perhaps something like this? pls check for the results

Another method...

Also, I noticed all the entries on Renewed and Expired are unique. Sorta messes things up if they are not. I copied one from Expired to Renewed. Get a "Y" in both renewed and expired columns (I tried it in azumi's file and it occurs there too). Makes me wonder how these are getting entered... as in if one is expired and not renewed, does it get removed from the expired list if it is renewed. Seems like a lot of extra effort when the entries could be all on one sheet and marked or flagged as they occur.

Yes indeed this is what I meant.

I had some luck with the COUNTIFS method too, after I added the new column of data. Interesting to see someone else's approach at it. I think the main issue is the to do with being able to tell them apart properly, like you said, line number and ref alone can leave situations where you can't. I think there is a way I can build this into the real thing by adding in a new column of data that can be made available.

Thanks for the insight, I definitely got something worth while out of this.