formula issues in tables with LED, MINIFS, MAXIFS functions

E

ED38

New Member
Joined
Mar 29, 2024
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Hello could you help with the formual in E2 and F2 as detailed below :
Many thank for your help


Book2
ABCDEFGHIJKLMNOPQ
1Table 1Product CodeQTYClose Date1st version dateMax versionTable 2ProductCodeLastUpdateVersionMinQuantityMaxQuantityValidFromValidUpToMax version
2AB83512023-01-27#SPILL!#SPILL!For each row in table1, I am looking for 2 things :AB8352023-11-034,411999 999 99945 23345 6904,41
3AC07262023-02-14#SPILL!#SPILL!AC0722023-11-034,211999 999 99945 23345 6624,21
4AO937112022-04-01#SPILL!#SPILL!The 1st update date matching the following conditions :AC0722025-01-065,901999 999 99945 66345 6905,90
5AC073132022-04-01#SPILL!#SPILL!Table2[productCode]=Table1[product Code]AC0712025-01-065,891999 999 99945 66345 6905,89
6AB150142022-04-01#SPILL!#SPILL!Table2[MinQuantity]<=Table1[QTY]AC0732025-01-065,891999 999 99945 66345 6905,89
7AA09642022-04-01#SPILL!#SPILL!Table2[MaxQuantity]>=Table1[QTY]AB1502023-11-034,211999 999 99945 23345 6904,21
8AA087172022-04-01#SPILL!#SPILL!=Min of Table2[LastUpdate]AA0962023-11-034,211999 999 99945 23345 6204,21
9AA090202023-03-31#SPILL!#SPILL!AA0962024-11-255,371999 999 99945621456905,37
10AB37162023-03-31#SPILL!#SPILL!If no date for a product code then I would put today()+365AA0872023-11-033,651999 999 99945 23345 6203,65
11AA06432023-03-31#SPILL!#SPILL!AA0872023-11-023,631999 999 99945 23245 2323,63
12AB83532023-03-31#SPILL!#SPILL!AA0872023-11-023,531 000 001999 999 99945232456903,53
13AC07292023-03-31#SPILL!#SPILL!AA0872023-11-023,5311100 00045232456903,53
14AO937142022-01-14#SPILL!#SPILL!The lastest version available(in table 2) at the closed date (in table 1)AA0872023-11-023,5311045232452323,63
15AC073122023-09-19#SPILL!#SPILL!matching the following conditions :AA0872023-11-023,53100 0011 000 00045232456903,53
16AB15052022-10-31#SPILL!#SPILL!Table2[productCode]=Table1[product Code]AA0872024-11-255,245 001999 99945621456625,24
17AA096172022-10-31#SPILL!#SPILL!Table2[MinQuantity]<=Table1[QTY]AA0872024-11-255,235015 00045 62145 6625,23
18AA087182022-11-09#SPILL!#SPILL!Table2[MaxQuantity]>=Table1[QTY]AA0872024-11-255,33150045 62145 6625,33
19AA090112022-07-30#SPILL!#SPILL!Table2[ValidFrom]<=Table1[ClosedDate]AA0872023-11-023,601 001999 999 99945 23245 6903,60
20AB371202022-07-30#SPILL!#SPILL!Table2[ValidUpTo]>=Table1[ClosedDate]AA0872023-11-023,601011 00045 23245 6903,60
21AA064202023-01-03#SPILL!#SPILL!=Table2[Max version]AA0872023-11-023,62110045 23245 2323,63
22AB835102023-01-03#SPILL!#SPILL!AA0872025-01-065,745015 00045663456905,74
23AC072162023-07-28#SPILL!#SPILL!AA0872025-01-065,765 001999 99945 66345 6905,76
24AO937192024-05-23#SPILL!#SPILL!AA0872025-01-065,81150045 66345 6905,81
25AC073182021-12-24#SPILL!#SPILL!AA0902023-11-033,651999 999 99945233456203,65
26AB15092021-10-01#SPILL!#SPILL!AA0902023-11-023,631999 999 99945 23245 2323,63
27AA096182021-12-21#SPILL!#SPILL!AA0902023-11-023,5311045232452323,63
28AA08792022-07-12#SPILL!#SPILL!AA0902023-11-023,531130045232456903,57
29AA09092024-06-07#SPILL!#SPILL!AA0902023-11-023,531 001999 999 99945232456903,53
30AB37162024-06-07#SPILL!#SPILL!AA0902023-11-023,533011 00045232456903,53
31AA064192024-06-07#SPILL!#SPILL!AA0902023-11-023,601345 23245 2323,63
32AB83532022-03-18#SPILL!#SPILL!AA0902023-11-023,5711999 999 99945 23245 6903,57
33AC07262022-03-18#SPILL!#SPILL!AA0902023-11-023,5741045 23245 6903,57
34AO93722022-03-18#SPILL!#SPILL!AA0902024-11-255,311019 99945 62145 6905,31
35AC073132022-03-18#SPILL!#SPILL!AA0902024-11-255,801610045 62145 6905,80
36AB150162022-01-21#SPILL!#SPILL!AA0902024-11-255,1411545 62145 6905,14
37AA09692022-10-31#SPILL!#SPILL!AB3712024-12-165,391999 999 99945 64245 6905,39
38AA08752023-11-02#SPILL!#SPILL!AA0642023-11-034,251999 999 99945 23345 6204,25
39AA09052024-08-26#SPILL!#SPILL!AA0642023-05-152,6711045 06145 2322,67
40AB371172021-07-07#SPILL!#SPILL!AA0642023-05-052,62110045 05145 0602,62
41AA064202023-03-31#SPILL!#SPILL!AA0642023-05-022,6012045 04845 0502,60
42AB835162021-07-29#SPILL!#SPILL!AA0642023-05-052,6110120145051450602,61
43AC07242021-07-27#SPILL!#SPILL!AA0642023-05-152,64112045061456902,64
44AO93712023-09-25#SPILL!#SPILL!AA0642023-05-152,6410120045 06145 6902,64
45AC07342022-06-14#SPILL!#SPILL!AA0642023-05-152,642110045 06145 6902,64
46AB15092024-08-23#SPILL!#SPILL!AA0642023-05-152,96201999 999 99945 06145 6902,96
47AA09652023-05-12#SPILL!#SPILL!AA0642023-05-022,605110045 04845 6902,60
48AA08772022-04-27#SPILL!#SPILL!AA0642023-05-022,60215045 04845 0602,60
49AA09022022-04-27#SPILL!#SPILL!AA0642023-05-022,6020150045 04845 0502,60
50AB371132022-03-10#SPILL!#SPILL!AA0642023-05-022,6010120045 04845 0502,60
Sheet1
Cell Formulas
RangeFormula
E2:E50E2=IFERROR(LET(m,Table2[LastUpdate]*(Table2[ProductCode]=[@[Product Code]])*(Table2[MinQuantity]<=[@QTY])*(Table2[MaxQuantity]>=[@QTY]),MINIFS(m,m,">0")),TODAY()+365)
F2:F50F2=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:O50O2=[@LastUpdate]
 
Sort by date Sort by votes
You can't pass an array to MINIFS or MAXIFS, they need actual ranges. So something like:

Excel Formula: 
=LET(m,MINIFS(Table2[LastUpdate],Table2[ProductCode],[@[Product Code]],Table2[MinQuantity],"<="&[@QTY],Table2[MaxQuantity],">="&[@QTY]),if(m>0,m,TODAY()+365))
 
Upvote 0
Solution
Thank you RoryA, that works perfectly and thank you for the highlight on the limitation on MINIIFS and MAXIF.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
New formula for cumulative values
Replies
6
Views
264
djclements
djclements
E
  • Question Question
extracting data with sorted and subtotal
Replies
1
Views
130
earth
E
C
  • Solved
Averaging + & - times in Text format
2
Replies
10
Views
674
Alex Blakenburg
A
P
  • Locked
  • Question Question
Below formula which works on excel 365 only, but also need to be work on excel 2021...
Replies
1
Views
218
Fluff
Fluff
JenniferMurphy
  • Solved
Adding a new time causes wrong average
Replies
3
Views
303
awoohaw
awoohaw

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top