CODEWITHSUNDEEP
excel
osugirl7
osugirl7

Reputation: 27

Multi-Criteria Look-up to pull optimal value

I have a table Table1 with Part #s and the length being purchased. I also have another table Table2 with all the part #s and the different lengths that these parts are available in (to be cut from).

PartA could be stocked in 20ft, 22ft, or 40ft pieces. I'm wondering if there is a way for excel to look at each part in Table1 and compare to the lengths for that part in Table2 and based on the length it was sold at tell me which length it should be cut from (the optimal length with the least waste).

For Example: A customer purchased PartA and want's it in 42ft pieces and we stock 40ft, 44ft, & 48ft pieces. So I would pick the 44ft piece to make the order. I could then have another order for that same PartA for 20ft pieces, in this case I would select the 40ft. This is a very small representation of the possible sizes so it would have to evaluate each one individually based on their stock sizes. Not all parts are stocked in 3 different sizes, some are stocked in 1 size others more.

Table1: enter image description here

Table2: enter image description here

*There are typically many pieces in an order, for instance if I had an order for 12ft pieces I would use the 48ft to make 4 12ft pieces.

Upvotes: 1

Views: 41

Answers (1)

Justyna MK
Justyna MK

Reputation: 3563

Edited my answer using your exemplary dataset and additional requirement -

=INDEX($F$2:$F$8,SUMPRODUCT(MATCH(SMALL(IF(($E$2:$E$8=A2)*($F$2:$F$8>=B2),MOD($F$2:$F$8,B2),10^100),1),IF(($E$2:$E$8=A2)*($F$2:$F$8>=B2),MOD($F$2:$F$8,B2)),0)))

Note that this is an array function so needs to be entered using Ctrl+Shift+Enter.

Results:

enter image description here

Upvotes: 1

Related Questions