Spreadsheet: 809720d1671265384-dynamic-array-need-to-populate-multiple-rows-and-columns-dynarrayfx-for-multiple-cols-ans
Sheet: Sheet1

 |  | 100000
 | ID | Value | Probability | Start | End
 | PRJ01 | 10000 | 0.25 | 2022-12-31 00:00:00 | 2023-01-20 00:00:00 |  |  |  | 2022-12-31 00:00:00 | 2023-01-07 00:00:00 | 2023-01-14 00:00:00 | 2023-01-21 00:00:00 | 2023-01-28 00:00:00 | 2023-02-04 00:00:00 | 2023-02-11 00:00:00 | 2023-02-18 00:00:00
 | PRJ02 | 20000 | 0.5 | 2023-01-14 00:00:00 | 2023-03-10 00:00:00 |  | 2 | PRJ01 | =LET(a,XLOOKUP(I5:I8,TblData[ID],TblData[End]),b,XLOOKUP(I5:I8,TblData[ID],TblData[Start]),c,J4:Q4,d,(c>=b)*(c<=a),d*H5:H8)
 | [NAME] | 30000 | 0.75 | 2023-01-07 00:00:00 | 2023-02-17 00:00:00 |  | 4 | PRJ02
 | PRJ04 | 40000 | 0.9 | 2023-02-11 00:00:00 | 2023-03-17 00:00:00 |  | 6 | [NAME]
 | PRJ05 | 50000 | 1 | 2023-01-21 00:00:00 | 2023-03-10 00:00:00 |  | 8 | PRJ04
 | PRJ06 | 30000 | 0.1 | 2022-12-31 00:00:00 | 2023-03-24 00:00:00
 | PRJ07 | 5000 | 0.5 | 2022-12-31 00:00:00 | 2023-01-20 00:00:00
 | PRJ08 | 12000 | 0.6 | 2023-02-04 00:00:00 | 2023-02-17 00:00:00
 |  |  |  |  | PROBLEM: | Get Resource allocation per week = Value/100,000 * Probability% * Allocation%, per relevant week, in a single dynamic array formula |  |  |  |  |  |  |  |  |  |  |  |  | MOCKUP
 | ID | Role | Allocation | Start | End | 2022-12-31 00:00:00 | 2023-01-07 00:00:00 | 2023-01-14 00:00:00 | 2023-01-21 00:00:00 | 2023-01-28 00:00:00 | 2023-02-04 00:00:00 | 2023-02-11 00:00:00 | 2023-02-18 00:00:00 | 2023-02-25 00:00:00 | 2023-03-04 00:00:00 | 2023-03-11 00:00:00 | 2023-03-18 00:00:00 |  | 2022-12-31 00:00:00 | 2023-01-07 00:00:00 | 2023-01-14 00:00:00 | 2023-01-21 00:00:00 | 2023-01-28 00:00:00 | 2023-02-04 00:00:00 | 2023-02-11 00:00:00 | 2023-02-18 00:00:00 | 2023-02-25 00:00:00 | 2023-03-04 00:00:00 | 2023-03-11 00:00:00 | 2023-03-18 00:00:00
 | PRJ01 | Lead Engineer | 1 |  |  |  | 0.025 | 0.025 |  |  |  |  |  |  |  |  |  |  | 0.025 | 0.025 | 0.025
 | PRJ01 | Project Mgr | 0.25 | 2022-12-31 00:00:00 | 2023-01-20 00:00:00 | 0.00625 | 0.00625 | 0.00625 |  |  |  |  |  |  |  |  |  |  | 0.00625 | 0.00625 | 0.00625
 | PRJ01 | Surveyor | 0.5 | 2022-12-31 00:00:00 | 2023-01-20 00:00:00 | 0.0125 | 0.0125 | 0.0125 |  |  |  |  |  |  |  |  |  |  | 0.0125 | 0.0125 | 0.0125
 | PRJ02 | Lead Engineer | 1 | 2023-01-14 00:00:00 | 2023-03-10 00:00:00 |  |  | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 |  |  |  |  |  | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1 | 0.1
 | PRJ02 | Project Mgr | 0.25 | 2023-01-14 00:00:00 | 2023-03-10 00:00:00 |  |  | 0.025 | 0.025 | 0.025 | 0.025 | 0.025 | 0.025 | 0.025 | 0.025 |  |  |  |  |  | 0.025 | 0.025 | 0.025 | 0.025 | 0.025 | 0.025 | 0.025 | 0.025
 | PRJ02 | Surveyor | 0.5 | 2023-01-14 00:00:00 | 2023-03-10 00:00:00 |  |  | 0.05 | 0.05 | 0.05 | 0.05 | 0.05 | 0.05 | 0.05 | 0.05 |  |  |  |  |  | 0.05 | 0.05 | 0.05 | 0.05 | 0.05 | 0.05 | 0.05 | 0.05
 | [NAME] | Lead Engineer | 1 | 2023-01-07 00:00:00 | 2023-02-17 00:00:00 |  | 0.225 | 0.225 | 0.225 | 0.225 | 0.225 | 0.225 |  |  |  |  |  |  |  | 0.225 | 0.225 | 0.225 | 0.225 | 0.225 | 0.225
 | [NAME] | Project Mgr | 0.25 | 2023-01-07 00:00:00 | 2023-02-17 00:00:00 |  | 0.05624999999999999 | 0.05624999999999999 | 0.05624999999999999 | 0.05624999999999999 | 0.05624999999999999 | 0.05624999999999999 |  |  |  |  |  |  |  | 0.05624999999999999 | 0.05624999999999999 | 0.05624999999999999 | 0.05624999999999999 | 0.05624999999999999 | 0.05624999999999999
 | [NAME] | Surveyor | 0.5 | 2023-01-07 00:00:00 | 2023-02-17 00:00:00 |  | 0.1125 | 0.1125 | 0.1125 | 0.1125 | 0.1125 | 0.1125 |  |  |  |  |  |  |  | 0.1125 | 0.1125 | 0.1125 | 0.1125 | 0.1125 | 0.1125
 | PRJ04 | Lead Engineer | 1 | 2023-02-11 00:00:00 | 2023-03-17 00:00:00 |  |  |  |  |  |  | 0.36 | 0.36 | 0.36 | 0.36 | 0.36 |  |  |  |  |  |  |  |  | 0.36 | 0.36 | 0.36 | 0.36 | 0.36
 | PRJ04 | Project Mgr | 0.25 | 2023-02-11 00:00:00 | 2023-03-17 00:00:00 |  |  |  |  |  |  | 0.09000000000000001 | 0.09000000000000001 | 0.09000000000000001 | 0.09000000000000001 | 0.09000000000000001 |  |  |  |  |  |  |  |  | 0.09000000000000001 | 0.09000000000000001 | 0.09000000000000001 | 0.09000000000000001 | 0.09000000000000001
 | PRJ04 | Surveyor | 0.5 | 2023-02-11 00:00:00 | 2023-03-17 00:00:00 |  |  |  |  |  |  | 0.18 | 0.18 | 0.18 | 0.18 | 0.18 |  |  |  |  |  |  |  |  | 0.18 | 0.18 | 0.18 | 0.18 | 0.18
 | PRJ05 | Lead Engineer | 1 | 2023-01-21 00:00:00 | 2023-03-10 00:00:00 |  |  |  | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 |  |  |  |  |  |  | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5 | 0.5
 | PRJ05 | Project Mgr | 0.25 | 2023-01-21 00:00:00 | 2023-03-10 00:00:00 |  |  |  | 0.125 | 0.125 | 0.125 | 0.125 | 0.125 | 0.125 | 0.125 |  |  |  |  |  |  | 0.125 | 0.125 | 0.125 | 0.125 | 0.125 | 0.125 | 0.125
 | PRJ05 | Surveyor | 0.5 | 2023-01-21 00:00:00 | 2023-03-10 00:00:00 |  |  |  | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 |  |  |  |  |  |  | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25 | 0.25
 | PRJ06 | Lead Engineer | 1 | 2022-12-31 00:00:00 | 2023-03-24 00:00:00 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 |  | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03 | 0.03
 | PRJ06 | Project Mgr | 0.25 | 2022-12-31 00:00:00 | 2023-03-24 00:00:00 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 |  | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075 | 0.0075
 | PRJ06 | Surveyor | 0.5 | 2022-12-31 00:00:00 | 2023-03-24 00:00:00 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 |  | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015 | 0.015
 | PRJ07 | Lead Engineer | 1 | 2022-12-31 00:00:00 | 2023-01-20 00:00:00 | 0.025 | 0.025 | 0.025 |  |  |  |  |  |  |  |  |  |  | 0.025 | 0.025 | 0.025
 | PRJ07 | Project Mgr | 0.25 | 2022-12-31 00:00:00 | 2023-01-20 00:00:00 | 0.00625 | 0.00625 | 0.00625 |  |  |  |  |  |  |  |  |  |  | 0.00625 | 0.00625 | 0.00625
 | PRJ07 | Surveyor | 0.5 | 2022-12-31 00:00:00 | 2023-01-20 00:00:00 | 0.0125 | 0.0125 | 0.0125 |  |  |  |  |  |  |  |  |  |  | 0.0125 | 0.0125 | 0.0125
 | PRJ08 | Lead Engineer | 1 | 2023-02-04 00:00:00 | 2023-02-17 00:00:00 |  |  |  |  |  | 0.072 | 0.072 |  |  |  |  |  |  |  |  |  |  |  | 0.072 | 0.072
 | PRJ08 | Project Mgr | 0.25 | 2023-02-04 00:00:00 | 2023-02-17 00:00:00 |  |  |  |  |  | 0.018 | 0.018 |  |  |  |  |  |  |  |  |  |  |  | 0.018 | 0.018
 | PRJ08 | Surveyor | 0.5 | 2023-02-04 00:00:00 | 2023-02-17 00:00:00 |  |  |  |  |  | 0.036 | 0.036 |  |  |  |  |  |  |  |  |  |  |  | 0.036 | 0.036