+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Registered User
    Join Date
    06-07-2019
    Location
    Minnesota, USA
    MS-Off Ver
    2016
    Posts
    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!
    Last edited by ufdlim; 06-07-2019 at 02:12 PM.

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    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)
    Last edited by 63falcondude; 06-07-2019 at 02:08 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 10-09-2018, 01:11 PM
  2. [SOLVED] Lookup to match multiple criteria and return multiple results excluding duplicates.
    By kris3012 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-13-2018, 11:06 AM
  3. Replies: 2
    Last Post: 05-26-2015, 07:29 PM
  4. Replies: 0
    Last Post: 10-16-2013, 12:42 PM
  5. lookup multiple values in multiple columns to return a result
    By AYAHOO123 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-11-2013, 07:53 PM
  6. Return Multiple Values with multiple rows & multiple columns
    By sachin parab in forum Excel General
    Replies: 2
    Last Post: 11-05-2012, 10:21 AM

Bookmarks

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