Spreadsheet: 818849d1677079931-xlookup-row-and-col-not-finding-matching-value-in-matrix-loan-calculator
Sheet: Sheet2

Completion Months | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10
1 | 1
2 | 0.66 | 0.34
3 | 0.37 | 0.41 | 0.22
4 | 0.22 | 0.4 | 0.24 | 0.14
5 | 0.2 | 0.3 | 0.22 | 0.2 | 0.08
6 | 0.18 | 0.19 | 0.21 | 0.2 | 0.11 | 0.11
7 | 0.14 | 0.15 | 0.2 | 0.2 | 0.11 | 0.1 | 0.1
8 |  | 0.11 | 0.2 | 0.2 | 0.12 | 0.12 | 0.07 | 0.07
9
10 | 0.1 | 0.1 | 0.15 | 0.15 | 0.11 | 0.11 | 0.1 | 0.09 | 0.04 | 0.05
 | Start Date |  |  | Cost | 2023-01-01 00:00:00 | 2023-02-01 00:00:00 | 2023-03-01 00:00:00 | 2023-04-01 00:00:00 | 2023-05-01 00:00:00 | 2023-06-01 00:00:00 | 2023-07-01 00:00:00 | 2023-08-01 00:00:00 | 2023-09-01 00:00:00 | 2023-10-01 00:00:00 | 2023-11-01 00:00:00 | 2023-12-01 00:00:00 | 2024-01-01 00:00:00 | 2024-02-01 00:00:00 | 2024-03-01 00:00:00 | 2024-04-01 00:00:00 | 2024-05-01 00:00:00 | 2024-06-01 00:00:00 | 2024-07-01 00:00:00 | 2024-08-01 00:00:00 | 2024-09-01 00:00:00 | 2024-10-01 00:00:00 | 2024-11-01 00:00:00 | 2024-12-01 00:00:00
 | 2023-03-01 00:00:00 |  | 1
 | 2023-04-01 00:00:00 |  | 2
 | 2023-05-01 00:00:00 |  | 3
 | 2023-06-01 00:00:00 |  | 4
 | 2023-07-01 00:00:00 |  | 5
 | 2023-08-01 00:00:00 |  | 6
 | 2023-09-01 00:00:00 |  | 7
 | 2023-10-01 00:00:00 |  | 8
 | 2023-11-01 00:00:00 |  | 9
 | 2023-12-01 00:00:00 |  | 10
 |  |  |  |  | =IF(OR(F$15<$B16,F$15>C16),"",IF($B16=F$15,XLOOKUP(1,months,XLOOKUP($D16,duration,SCALE)*$E16,IF(F$15>$B16,XLOOKUP(COUNTIF($F16:F16,">0"),months,XLOOKUP($D16,duration,SCALE)*$E16)))))
 |  |  |  |  | If the Month in row 15 is LESS than the Start Month in Col B, cell is blank.
 |  |  |  |  | Works- as shown in F16 and G16.
 |  |  |  |  | If the Month in row 15 is AFTER the End Date in Col C, cell is blank.
 |  |  |  |  | Not working - as shown in H16 onward.
 |  |  |  |  | If the Month in row 15 MATCHES the Start Month in B16 - B25 (so Month=1), find the row in B2 - B11 that matches the Prod Time value in Col D, and multiply it by the Cost.
 |  |  |  |  | Works- as shown in rows 16 - 25
 |  |  |  |  | If the Month in row 15 is LATER than the Start Month in B16 - B25, count the number of cols in row 16 that are not blank, match that number to Row 1, and the value in A2 - A11 that matches Col D, amd multiply the percentage by the cost in [NAME]
 |  |  |  |  | Not working - as shown in H16 onward.
 |  |  |  |  |  | 0 | 1480000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 | 0 | 0 | 1320000 | 680000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 | 0 | 0 | 0 | 555000 | 615000 | 330000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 264000 | 480000 | 288000 | 168000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 260000 | 390000 | 286000 | 260000 | 104000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 234000 | 247000 | 273000 | 260000 | 143000 | 143000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 182000 | 195000 | 260000 | 260000 | 143000 | 130000 | 130000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 143000 | 143000 | 260000 | 260000 | 156000 | 156000 | 91000.00000000001 | 91000.00000000001 | 0 | 0 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 160333.3333333333 | 160333.3333333333 | 160333.3333333333 | 177666.6666666667 | 177666.6666666667 | 177666.6666666667 | 95333.33333333333 | 95333.33333333333 | 95333.33333333333 | 0 | 0 | 0 | 0 | 0
 |  |  |  |  | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 130000 | 130000 | 195000 | 195000 | 143000 | 143000 | 130000 | 117000 | 52000 | 65000 | 0 | 0 | 0