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

Hi,

Hopefully the example I have made shows clearly what I am asking....

There is a list of refs and line numbers to go with them, there can be duplicates of the refs for multiple client contracts, each will have a unique line number against that ref to tell them apart.

There are 3 sheets: a list of ones due for renewal, a list of ones renewed, and a list of ones expired.

I want to put formulas in the 3 columns for 'Renewed', 'Replaced', and 'Expired'.

I have already done the Renewed column with this formula: (Array)

Formula:

{=IF(NOT(ISERROR(MATCH(A2&B2,Renewed!$A$2:$A$5&Renewed!$B$2:$B$5,0))),"Y","")}

And this works fine.

The problem with the other 2 columns is that one could be expired but then also recreated against that ref with a new line number. In that case it will appear on the expired list matching both ref and line number, and it will also appear on the renewed list matching the ref but with a new line number that didn't exist for that ref on the due list. The line number could exists for other refs on the due list though.

So I need to match both columns A&B on the due list to columns A&B on Expired, but exclude if column A also matches the renewed lists while column B does not.

As I said hopefully the example makes it clear what I am asking.

Many thanks in advance for any help on this problem.