Replace MAX with MIN bur greater than

Arek92

New Member
Joined
Jan 29, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a warehouse master list with several item no. positions. Each item no. occurs more than once, and each occurence has its own bin code, lot no. and quantity. For example, an item no. may be located in several different bins nad have several different lot numbers. A bin may contain several different items, each with its own lot no, as well same two or more item numbers of the same lot no., etc.
In my other tab I have a list of items with required quantity for each item. I managed to build a formula that looks up single items from the master list that match the greater than or equal to the required quantity condition, and returns their bin code and lot no. However, some of the items in the master list do not meet the required quantity condition, and the formula returns an error. I managed to build another formula that adds up all items with the same item no. per (not in) every lot no. per every bin. Other formuls then return the bin and lot no. that contains the largest summed quantity of an item.
What I'm looking for is to lookup a bin and lot no. of the minimal item's qty sum that meets the greater than or equal to required qty condition. If my understanding is right, I need to somehow replace the MAX in my formula with a MIN and IF, but couldn't seem to have any luck with that. Any help would be appreciated.

project.xlsx
ABCDEFG
1ItemRequired qtyMatching qty binMatching qty lot no.Mathcing qtyMax sum lot no.Max sum bin code
2101100#N/D#N/D01A1/2/2
3102201/3/22A202A1/2/1
4103125#N/D#N/D03B2/2/1
5104353/2/34D354A3/2/1
6105100#N/D#N/D05A2/7/2
7106104/4/46B156C4/4/6
PickList
Cell Formulas
RangeFormula
C2:C7C2=INDEX(Bins,MATCH(1,([@[Mathcing qty]]=Bins[Qty])*([@Item]=Bins[Item]),0),2)
D2:D7D2=INDEX(Bins,MATCH(1,([@[Mathcing qty]]=Bins[Qty])*([@Item]=Bins[Item]),0),3)
E2:E7E2=MIN(IF([@Item]=Bins[Item],IF([@[Required qty]]<=Bins[Qty],Bins[Qty])))
F2:F7F2=INDEX(Bins[Lot no.],MATCH(MAX(SUMIF(Bins[Lot no.],IF([@Item]=Bins[Item],Bins[Lot no.]),Bins[Qty])),SUMIF(Bins[Lot no.],IF([@Item]=Bins[Item],Bins[Lot no.]),Bins[Qty]),0))
G2:G7G2=INDEX(Bins[Bin],MATCH(MAX(SUMIF(Bins[Bin],IF([@Item]=Bins[Item],Bins[Bin]),Bins[Qty])),SUMIF(Bins[Bin],IF([@Item]=Bins[Item],Bins[Bin]),Bins[Qty]),0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Arek92

New Member
Joined
Jan 29, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
And here's my master list tab. I'm sorry, I didn't know it'll only add the other tab.

project.xlsx
ABCD
1ItemBinLot no.Qty
21011/1/11B35
31011/1/11C20
41011/1/21A50
51011/1/21A30
61011/1/21A40
71011/1/71A5
81011/2/21A40
91011/2/21B50
101011/2/21A60
111021/2/12A70
121021/3/22A20
131022/1/12B55
141031/1/13A5
151031/2/53A45
161032/1/33B20
171032/1/43A40
181032/1/43A50
191032/1/43A80
201032/2/13B30
211032/2/13B60
221032/2/13B40
231032/2/13B65
241032/2/23B30
251032/2/73C15
261043/2/14A40
271043/2/34D35
281051/4/25B45
291052/5/15A50
301052/5/15C35
311052/7/25A60
321052/7/25A40
331064/4/46B15
341064/4/66C60
MasterData
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,441
Members
417,025
Latest member
MusterDuster

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
Top