Excel formula to compare two columns in workbook A to two columns in workbook B?

Hi

this formula doen't require the column C where A3&B3

HTML Code: 

Hello,

Thank you very much Sadath31! The formula works perfectly. Could I be as ignorant to ask for an explanation of the formula, for my own understanding what does the "--" do? also why the use of sumproduct?

Thank you.

Hi

Glad to help you..

the double hyphen is converting a list of boolean (true, false) values into ZEROs and ONEs.

the first statement --(A2='[Euro HY Pipeline - WEB CD.xlsx]2019 Pipeline'!$A$3:$A$140) return a array of values consist of 0 and 1
ie if A2='[Euro HY Pipeline - WEB CD.xlsx]2019 Pipeline'!$A$1 is true then return 1
ie if A2='[Euro HY Pipeline - WEB CD.xlsx]2019 Pipeline'!$A$2 is false then return 0 .... so on

the second statement
B2-'[Euro HY Pipeline - WEB CD.xlsx]2019 Pipeline'!$B$3:$B$140 this returns array of values which is difference of dates

ABS(B2-'[Euro HY Pipeline - WEB CD.xlsx]2019 Pipeline'!$B$3:$B$140) converts -ve value into +ve

--ABS(B2-'[Euro HY Pipeline - WEB CD.xlsx]2019 Pipeline'!$B$3:$B$140)<=7 check the values is <=7 then return 1 (for true) or 0 (for false)

Finally SUMPRODUCT multiplies both array and add together and return a number which is number of records matching the given criteria

if the result is 0 that means no matching record, if the result is >0 then there is matching record.

Hope this help you.