This is probably one of the most complex lookups I've tried. I can easily do it on paper but not over 3000 line items. The setup is below. Basically, I have a bunch of straggler products of odd sizes I'm trying to group into existing buckets if they fall within +/- 20% of the bucket size.
Table 1: Existing Buckets
ProductID Product Name Package Size 100 Product A 118 100 Product A 15.6 10014 Product B 100 10014 Product B 250
Table 2: Straggler Products
Product ID Package Size Existing Bucket? (TRUE/FALSE) 100 120 (Should be TRUE - as 118 is within 20%) 100 473 (Should be FALSE - as neither 118 or 15.6 is within 20%) 10014 115 (Should be TRUE) 10014 225 (Should be TRUE)
I think splitting up Package Size in Table 1 into upper and lower bounds of the 20% and then using if statements for subtracting the value in Table 2 might be the right direction. E.g. if Lower Bound minus the Table 2 Package size is NEGATIVE, it would be within the range. And if Upper Bound minus Table 2 Package size is POSITIVE, it would be within the range.
I think maybe a combination off INDEX, MATCH, and IF statements? Any help would be appreciated!
Bookmarks