IF/INDEX/MATCH?? - for looking up specific cells and quantities

[RickCov]

Hi,


I'm looking for a way to simplify a parts pick process*

where all data input and output is manual as we currently

have no stock system in the workplace


It's not going to be easy to explain but here goes..


I need a Formula to look up multiple locations and quantities

for a given part number.


The look up must return locations and quantities with*

sufficent quantity to pick.


In the example attached the lookup for part number 20003080 should*

return "AM3402B - 450" from row 14 on the stocksheet tab,*

as row 13 does not have sufficient quantity.


I know what I want to acheive but unfortunately my excel*

knowledge does not reach that far..


Thanks

Richard

[rorya]

What if there's more than one location with enough stock? Does it matter which one is returned?

[PaulM100]

I assumed that you are interested in the largest location and that you need both the location and qty in the same cell:

Please Login or Register  to view this content.

It is an array formula so you need ctrl+shift+enter instead of regular enter to take effect.
If you do not need both in the same cell then use only the MAX formula

[RickCov]

Hi Rory, It doesn't matter which one is returned however if one location is found which only partly fulfills the ddesired pick quantity, a second location would be helpful to further fulfill the requirement.. If that makes sense..!?

[RickCov]

Thanks Paul, this seems to work if a single location has sufficient stock. I will do some testing and feed back. Please also see my reply above re: a second location..

[PaulM100]

That is doable, change the MAX part to LARGE(IF('Stocksheet Sample'!$A$2:$A$69='Pick Sample'!A2,'Stocksheet Sample'!$D$2:$D$69),2) and you will find the second largest value and the location for it. But, as an example, for 20001038, both qty summed will not fulfill the requested qty, so, that means that you will need to add a third location.

[Richard Buttrey]

I'd be inclined to use a Pivot Table - with an optional slicer to pick a particular product ID

See attached