I have 2 tables, one that has 2 columns, Date and Amount and another table that has only a Date column.

The 2nd table has a wider range of Dates and I'm aiming towards matching the Dates from 1st table with 2nd table and record appropriate Amounts in the 2nd table, as well as $0.00 values for empty rows, where there are NO dates to match from 1st table in 2nd.

So 2nd table should contain it's default Date's and Amounts column added from 1st table beside the Dates $0.00 values where Amounts don't exist.

I'm aiming for ARRAYFORMULA to do the matching, so fields are added automatically in 2nd table as I upgrade 1st table, without having to drag formula in 2nd table for every field added to update the sheet.

Here's the formula I came up with and was wondering what's wrong with it or whether my result can be achieved dynamically.

=ARRAYFORMULA(IF(COUNTIF($A$1:$A,$D$1:D)>0, QUERY({$A$1:$B}, "SELECT Col2 WHERE Col1 = $D$1:D"), 0 ))

Please Help.

Thanks.

https://docs.google.com/spreadsheets/d/1wCK-eyGdpMS605XVCBQDHy_5LYkbkWaaHZA2bNSY1SQ/edit?usp=sharing

CodePudding user response：

Have you tried vlookup like this (cell E1):

=arrayformula(if(D1:D<>"",iferror(vlookup(D1:D,A:B,2,0),0),))

It'll only work with unique dates though.