Help with formula please, if two values match calculate the difference between 2 dates

[Newbie73]

Hello, I need help creating a formula, example sheet:

Test4.xlsx

The goal is to if a value in column D matches the exact same value in column A the calculate the difference, in days, of the respective dates (as in, the same row date as the values). I've done the example sheet results in column G

Details:

Values from A and D won't be in order, so they need to be matched and then calculate the dates in front of the respectice rows for each A and D value;
Dates can change in both ways, as in sometimes the date in B might be more recent than the one in column E or vice versa;
Sometimes there might not be a match between A and D and sometimes there might not be a date, so ideally to ignore/blank cell this situations please

Thank you in advance for your help! Let me know if you need any more information or if I didn't explain something correctly.

 

[hagia_sofia]

Hello, the question is how to handle duplicate records on both sides, i.e. if there is more than 1 match, what is decisive for subtraction?

 

[Newbie73]

Hello and thanks for the reply! Forgot to mention that, yes there will be duplicates sometimes but it is only in column D. Column A in the example sheet has duplicates by mistake and it shouldn't have happened sorry, theres only unique values in Column A in the real spreadsheet.

 

[hagia_sofia]

Thanks for the reply. Would this suit your cause (cf. also the file):

=ABS(XLOOKUP(D2:D15,$A$2:$A$15,$B$2:$B$15)-E2:E15)

 

[Newbie73]

Thanks for the reply. Would this suit your cause (cf. also the file):

=ABS(XLOOKUP(D2:D15,$A$2:$A$15,$B$2:$B$15)-E2:E15)

Looks promising! Don't have enough time now but will double check tomorrow morning first thing. One quick thing I noticed, is there any chance of having blank cell instead of errors? In the real spreadsheet, when there's no match between the two values (which is normal and it will happen) there's #N/A, but ideally it would be a blank cell please

Thank you so much!

 

[hagia_sofia]

Ah, indeed, forgot about that:

=IFNA(ABS(XLOOKUP(D2:D15,A2:A15,B2:B15)-E2:E15),"")

 

[Newbie73]

Thank you so much for your help! Working as intended

 

[Newbie73]

Hello Hagia Sofia! Sorry being a pain, but could I ask if a small change in the formula is possible? So on the example sheet, if the date on B is more recent than the date on E to ignore it or blank cell result.

Would this be possible?

 

[hagia_sofia]

Hello again, do you mean this:

=LET(
a,XLOOKUP(D2:D15,A2:A15,B2:B15)-E2:E15,
IFNA(IF(a<0,"",a),""))

 

[Newbie73]

Not quite, I've updated the example putting the numbers per order (they won't be per order in the real one, just to be easiest to see to this subject). I've highlighted the rows where the date on column B is more recent tha on E, which means this rows should be blank on the result. Hope it helps and thank you again for your help