Invoice Analysis with Date

[Shabbo]

Dear Sir,

I have two sheets one is Old Outstanding List as on Dec and another one New Outstanding List on 27 Mar.

I wanted to compare both sheets the following way.

1) If the Invoice is available in Dec sheet then New Outstanding List on 27 Mar Remarks column should show “Invoice Available in Dec”

2) If the Invoice is not available in Old Outstanding List and Available New Outstanding List but Invoice date is before 31 - 12 -2016 then remarks columns should show its “already settled”

3) If the Invoice is not available in Old Outstanding List and Available New Outstanding List but Invoice date is after 31 - 12 -2016 then remarks columns should show its “New Invoice”


I have given some example in remarks column.

 

[vletm]

@Shabbo
'New Outstanding List on 27 Mar'-sheet:
Write to Cell [E1] that Your date 31/12/2016
Write to Cell [C2] =IF(A2<>"",IFERROR(IF(MATCH(A2,'Old Outstanding List as on Dec'!A:A,0)>0,"Invoice Available in Dec","?"),IF(B2<E$1,"already settled","New Invoice")),"-")
... and copy as down as need.
Note: 'Date'
2) before 31 - 12 -2016
3) after 31 - 12 -2016
... hmm what s/would happen if 'Date' is 31/12/2016?

 

[Shabbo]

@Shabbo
'New Outstanding List on 27 Mar'-sheet:
Write to Cell [E1] that Your date 31/12/2016
Write to Cell [C2] =IF(A2<>"",IFERROR(IF(MATCH(A2,'Old Outstanding List as on Dec'!A:A,0)>0,"Invoice Available in Dec","?"),IF(B2<E$1,"already settled","New Invoice")),"-")
... and copy as down as need.
Note: 'Date'
2) before 31 - 12 -2016
3) after 31 - 12 -2016
... hmm what s/would happen if 'Date' is 31/12/2016?

what s/would happen if 'Date' is 31/12/2016? = new Invoice.

 

[vletm]

... hmmm ....
Invoice date is after 31 - 12 -2016 then remarks columns should show its “New Invoice”
>> okay ... no need changes in my 'formula'

 

[Shabbo]

what s/would happen if 'Date' is 31/12/2016? = new Invoice.

One more addition please bring amount in new sheet from sheet.

 

[vletm]

There were already solution for that too... but okay.