# Lookup multiple criteria in multiple columns in multiple rows; return true if exists

1. ## Lookup multiple criteria in multiple columns in multiple rows; return true if exists

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!

2. ## Re: Lookup multiple criteria in multiple columns in multiple rows; return true if exists

With Table 1 in A1:C5 and Table 2 in E1 to G5, try this in G2:

=IF(COUNTIFS(A:A,E2,C:C,">="&F2-0.2*F2,C:C,"<="&F2+0.2*F2),TRUE)

slightly shorter:

=IF(COUNTIFS(A:A,E2,C:C,">="&0.8*F2,C:C,"<="&1.2*F2),TRUE)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1