Compare Column with column of different table row by row according to dates

[Anonymous]

Hi All,

I have 2 tables one summary table  and other detailed table with historical data.

Table 1:-  

 Table 2:-

Table 2 has data for last 5 months.

I need to calculate Days in Table 1 using columns of Table 2:-

=IFERROR(IF(Inv<=Dec,Inv/Dec*31,IF(Inv<=(Dec+Nov),(Inv-Dec)/Nov*30+31,IF(Inv<=(Dec+Nov+Oct),(Inv-Dec-Nov)/Oct*31+31+30,IF(Inv<=(Dec+Nov+Oct+Sep),(Inv-Dec-Nov-Oct)/Sep*30+31+30+31,IF(Inv<=(Dec+Nov+Oct+Sep+Aug),(Inv-Dec-Nov-Oct-Sep)/Aug*31+31+30+31+30))),0)

Basically what formula does is, for a particular Code and a particular Month -  Inv should be compared with its previous months data,

In excel it is simple as i can pivot the table use RC reference formula, but here it should be dynamic.

For Month 01/2020 -  it will consider - Dec,Nov, Oct, Sep, Aug

For mont 02/2020 -Jan,Dec,Nov, Oct, Sep in the same formula above.

I tried using variables but due to the dates in same column, i am not getting the desired result.

Please help with this dynamic formula

Any help appreciated.

[v-lionel-msft]

Hi @Anonymous ,

I can understand the connection between your two tables, but I don’t quite understand what you want to calculate.

Maybe you can use a table to list the results you want to get.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.