Hello could you help with the formual in E2 and F2 as detailed below :
Many thank for your help
Many thank for your help
Book2 | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
1 | Table 1 | Product Code | QTY | Close Date | 1st version date | Max version | Table 2 | ProductCode | LastUpdate | Version | MinQuantity | MaxQuantity | ValidFrom | ValidUpTo | Max version | ||||
2 | AB835 | 1 | 2023-01-27 | #SPILL! | #SPILL! | For each row in table1, I am looking for 2 things : | AB835 | 2023-11-03 | 4,41 | 1 | 999 999 999 | 45 233 | 45 690 | 4,41 | |||||
3 | AC072 | 6 | 2023-02-14 | #SPILL! | #SPILL! | AC072 | 2023-11-03 | 4,21 | 1 | 999 999 999 | 45 233 | 45 662 | 4,21 | ||||||
4 | AO937 | 11 | 2022-04-01 | #SPILL! | #SPILL! | The 1st update date matching the following conditions : | AC072 | 2025-01-06 | 5,90 | 1 | 999 999 999 | 45 663 | 45 690 | 5,90 | |||||
5 | AC073 | 13 | 2022-04-01 | #SPILL! | #SPILL! | Table2[productCode]=Table1[product Code] | AC071 | 2025-01-06 | 5,89 | 1 | 999 999 999 | 45 663 | 45 690 | 5,89 | |||||
6 | AB150 | 14 | 2022-04-01 | #SPILL! | #SPILL! | Table2[MinQuantity]<=Table1[QTY] | AC073 | 2025-01-06 | 5,89 | 1 | 999 999 999 | 45 663 | 45 690 | 5,89 | |||||
7 | AA096 | 4 | 2022-04-01 | #SPILL! | #SPILL! | Table2[MaxQuantity]>=Table1[QTY] | AB150 | 2023-11-03 | 4,21 | 1 | 999 999 999 | 45 233 | 45 690 | 4,21 | |||||
8 | AA087 | 17 | 2022-04-01 | #SPILL! | #SPILL! | =Min of Table2[LastUpdate] | AA096 | 2023-11-03 | 4,21 | 1 | 999 999 999 | 45 233 | 45 620 | 4,21 | |||||
9 | AA090 | 20 | 2023-03-31 | #SPILL! | #SPILL! | AA096 | 2024-11-25 | 5,37 | 1 | 999 999 999 | 45621 | 45690 | 5,37 | ||||||
10 | AB371 | 6 | 2023-03-31 | #SPILL! | #SPILL! | If no date for a product code then I would put today()+365 | AA087 | 2023-11-03 | 3,65 | 1 | 999 999 999 | 45 233 | 45 620 | 3,65 | |||||
11 | AA064 | 3 | 2023-03-31 | #SPILL! | #SPILL! | AA087 | 2023-11-02 | 3,63 | 1 | 999 999 999 | 45 232 | 45 232 | 3,63 | ||||||
12 | AB835 | 3 | 2023-03-31 | #SPILL! | #SPILL! | AA087 | 2023-11-02 | 3,53 | 1 000 001 | 999 999 999 | 45232 | 45690 | 3,53 | ||||||
13 | AC072 | 9 | 2023-03-31 | #SPILL! | #SPILL! | AA087 | 2023-11-02 | 3,53 | 11 | 100 000 | 45232 | 45690 | 3,53 | ||||||
14 | AO937 | 14 | 2022-01-14 | #SPILL! | #SPILL! | The lastest version available(in table 2) at the closed date (in table 1) | AA087 | 2023-11-02 | 3,53 | 1 | 10 | 45232 | 45232 | 3,63 | |||||
15 | AC073 | 12 | 2023-09-19 | #SPILL! | #SPILL! | matching the following conditions : | AA087 | 2023-11-02 | 3,53 | 100 001 | 1 000 000 | 45232 | 45690 | 3,53 | |||||
16 | AB150 | 5 | 2022-10-31 | #SPILL! | #SPILL! | Table2[productCode]=Table1[product Code] | AA087 | 2024-11-25 | 5,24 | 5 001 | 999 999 | 45621 | 45662 | 5,24 | |||||
17 | AA096 | 17 | 2022-10-31 | #SPILL! | #SPILL! | Table2[MinQuantity]<=Table1[QTY] | AA087 | 2024-11-25 | 5,23 | 501 | 5 000 | 45 621 | 45 662 | 5,23 | |||||
18 | AA087 | 18 | 2022-11-09 | #SPILL! | #SPILL! | Table2[MaxQuantity]>=Table1[QTY] | AA087 | 2024-11-25 | 5,33 | 1 | 500 | 45 621 | 45 662 | 5,33 | |||||
19 | AA090 | 11 | 2022-07-30 | #SPILL! | #SPILL! | Table2[ValidFrom]<=Table1[ClosedDate] | AA087 | 2023-11-02 | 3,60 | 1 001 | 999 999 999 | 45 232 | 45 690 | 3,60 | |||||
20 | AB371 | 20 | 2022-07-30 | #SPILL! | #SPILL! | Table2[ValidUpTo]>=Table1[ClosedDate] | AA087 | 2023-11-02 | 3,60 | 101 | 1 000 | 45 232 | 45 690 | 3,60 | |||||
21 | AA064 | 20 | 2023-01-03 | #SPILL! | #SPILL! | =Table2[Max version] | AA087 | 2023-11-02 | 3,62 | 1 | 100 | 45 232 | 45 232 | 3,63 | |||||
22 | AB835 | 10 | 2023-01-03 | #SPILL! | #SPILL! | AA087 | 2025-01-06 | 5,74 | 501 | 5 000 | 45663 | 45690 | 5,74 | ||||||
23 | AC072 | 16 | 2023-07-28 | #SPILL! | #SPILL! | AA087 | 2025-01-06 | 5,76 | 5 001 | 999 999 | 45 663 | 45 690 | 5,76 | ||||||
24 | AO937 | 19 | 2024-05-23 | #SPILL! | #SPILL! | AA087 | 2025-01-06 | 5,81 | 1 | 500 | 45 663 | 45 690 | 5,81 | ||||||
25 | AC073 | 18 | 2021-12-24 | #SPILL! | #SPILL! | AA090 | 2023-11-03 | 3,65 | 1 | 999 999 999 | 45233 | 45620 | 3,65 | ||||||
26 | AB150 | 9 | 2021-10-01 | #SPILL! | #SPILL! | AA090 | 2023-11-02 | 3,63 | 1 | 999 999 999 | 45 232 | 45 232 | 3,63 | ||||||
27 | AA096 | 18 | 2021-12-21 | #SPILL! | #SPILL! | AA090 | 2023-11-02 | 3,53 | 1 | 10 | 45232 | 45232 | 3,63 | ||||||
28 | AA087 | 9 | 2022-07-12 | #SPILL! | #SPILL! | AA090 | 2023-11-02 | 3,53 | 11 | 300 | 45232 | 45690 | 3,57 | ||||||
29 | AA090 | 9 | 2024-06-07 | #SPILL! | #SPILL! | AA090 | 2023-11-02 | 3,53 | 1 001 | 999 999 999 | 45232 | 45690 | 3,53 | ||||||
30 | AB371 | 6 | 2024-06-07 | #SPILL! | #SPILL! | AA090 | 2023-11-02 | 3,53 | 301 | 1 000 | 45232 | 45690 | 3,53 | ||||||
31 | AA064 | 19 | 2024-06-07 | #SPILL! | #SPILL! | AA090 | 2023-11-02 | 3,60 | 1 | 3 | 45 232 | 45 232 | 3,63 | ||||||
32 | AB835 | 3 | 2022-03-18 | #SPILL! | #SPILL! | AA090 | 2023-11-02 | 3,57 | 11 | 999 999 999 | 45 232 | 45 690 | 3,57 | ||||||
33 | AC072 | 6 | 2022-03-18 | #SPILL! | #SPILL! | AA090 | 2023-11-02 | 3,57 | 4 | 10 | 45 232 | 45 690 | 3,57 | ||||||
34 | AO937 | 2 | 2022-03-18 | #SPILL! | #SPILL! | AA090 | 2024-11-25 | 5,31 | 101 | 9 999 | 45 621 | 45 690 | 5,31 | ||||||
35 | AC073 | 13 | 2022-03-18 | #SPILL! | #SPILL! | AA090 | 2024-11-25 | 5,80 | 16 | 100 | 45 621 | 45 690 | 5,80 | ||||||
36 | AB150 | 16 | 2022-01-21 | #SPILL! | #SPILL! | AA090 | 2024-11-25 | 5,14 | 1 | 15 | 45 621 | 45 690 | 5,14 | ||||||
37 | AA096 | 9 | 2022-10-31 | #SPILL! | #SPILL! | AB371 | 2024-12-16 | 5,39 | 1 | 999 999 999 | 45 642 | 45 690 | 5,39 | ||||||
38 | AA087 | 5 | 2023-11-02 | #SPILL! | #SPILL! | AA064 | 2023-11-03 | 4,25 | 1 | 999 999 999 | 45 233 | 45 620 | 4,25 | ||||||
39 | AA090 | 5 | 2024-08-26 | #SPILL! | #SPILL! | AA064 | 2023-05-15 | 2,67 | 1 | 10 | 45 061 | 45 232 | 2,67 | ||||||
40 | AB371 | 17 | 2021-07-07 | #SPILL! | #SPILL! | AA064 | 2023-05-05 | 2,62 | 1 | 100 | 45 051 | 45 060 | 2,62 | ||||||
41 | AA064 | 20 | 2023-03-31 | #SPILL! | #SPILL! | AA064 | 2023-05-02 | 2,60 | 1 | 20 | 45 048 | 45 050 | 2,60 | ||||||
42 | AB835 | 16 | 2021-07-29 | #SPILL! | #SPILL! | AA064 | 2023-05-05 | 2,61 | 101 | 201 | 45051 | 45060 | 2,61 | ||||||
43 | AC072 | 4 | 2021-07-27 | #SPILL! | #SPILL! | AA064 | 2023-05-15 | 2,64 | 11 | 20 | 45061 | 45690 | 2,64 | ||||||
44 | AO937 | 1 | 2023-09-25 | #SPILL! | #SPILL! | AA064 | 2023-05-15 | 2,64 | 101 | 200 | 45 061 | 45 690 | 2,64 | ||||||
45 | AC073 | 4 | 2022-06-14 | #SPILL! | #SPILL! | AA064 | 2023-05-15 | 2,64 | 21 | 100 | 45 061 | 45 690 | 2,64 | ||||||
46 | AB150 | 9 | 2024-08-23 | #SPILL! | #SPILL! | AA064 | 2023-05-15 | 2,96 | 201 | 999 999 999 | 45 061 | 45 690 | 2,96 | ||||||
47 | AA096 | 5 | 2023-05-12 | #SPILL! | #SPILL! | AA064 | 2023-05-02 | 2,60 | 51 | 100 | 45 048 | 45 690 | 2,60 | ||||||
48 | AA087 | 7 | 2022-04-27 | #SPILL! | #SPILL! | AA064 | 2023-05-02 | 2,60 | 21 | 50 | 45 048 | 45 060 | 2,60 | ||||||
49 | AA090 | 2 | 2022-04-27 | #SPILL! | #SPILL! | AA064 | 2023-05-02 | 2,60 | 201 | 500 | 45 048 | 45 050 | 2,60 | ||||||
50 | AB371 | 13 | 2022-03-10 | #SPILL! | #SPILL! | AA064 | 2023-05-02 | 2,60 | 101 | 200 | 45 048 | 45 050 | 2,60 | ||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E2:E50 | E2 | =IFERROR(LET(m,Table2[LastUpdate]*(Table2[ProductCode]=[@[Product Code]])*(Table2[MinQuantity]<=[@QTY])*(Table2[MaxQuantity]>=[@QTY]),MINIFS(m,m,">0")),TODAY()+365) |
F2:F50 | F2 | =IFERROR(LET(m,(Table2[ProductCode]=[@[Product Code]])*(Table2[MinQuantity]<=[@QTY])*(Table2[MaxQuantity]>=[@QTY])*(Table2[ValidFrom]<=[@[Close Date]])*(Table2[ValidUpTo]>=[@[Close Date]]),MAXIFS(m,m,">0")),"none") |
O2:O50 | O2 | =[@LastUpdate] |