+ Reply to Thread
Results 1 to 9 of 9

Multiple (Some Exact/ Some Not) Criteria Lookup

  1. 08-07-2013, 12:26 AM #1
    stats90
    stats90 is offline
    Registered User
    Join Date
    07-26-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Question Multiple (Some Exact/ Some Not) Criteria Lookup

    So I am trying to lookup a specific value in a very large spreadsheet (>100,000 rows). A sample spreadsheet is attached for reference.

    I am looking to return the value in the VALUE column. Usually this is a number but it could be a text string.

    There are 3 conditions a user sets (in the yellow highlighted cells) in order to find the correct VALUE. The first 2 are "Customer ID" and "Metric". These 2 should always produce a perfect match within the dataset.

    The last variable is the "Date of New Shipping Process". This is related to the "Shipping Date" but there will likely NOT be a direct match within the data set. I am interested in finding the "Shipping Date" that is CLOSEST to the "Date of New Shipping Process", regardless of whether it is before or after that date.

    Once the nearest specific shipping date has been identified that also matches the designated Customer ID and Metric, I am interested in returning the matching VALUE in column C.

    I have tried doing this nesting INDEX/MATCH array functions (eg Index(Range,Match(1,((A:A=Customer ID)*(B:B=Metric))...,0))) but I don't know how to find the CLOSEST shipping date from there. I've also tried using a Match(min(abs(range-"Date of New Shipping Process"))) theme to no avail.

    Any ideas would be MUCH appreciated...

    Thanks!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-07-2013, 02:05 AM #2
    Zantax
    Zantax is offline
    Registered User
    Join Date
    10-23-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Multiple (Some Exact/ Some Not) Criteria Lookup

    Workbook1(1)-SHIPPING DATES.xlsx

    Are you able to add additional columns somwhere in the workbook, without affecting any processes?

    I have moved over the section that you highlighted in yellow, and added two new columns which calculate the number of days between the two dates, with a second column that converts all numbers to a positive number. (See attachment)

    Can you see something like this working for you in the formula?
    STaylor
    The Man
    Reply With QuoteRegister To Reply
  3. 08-07-2013, 05:32 AM #3
    L-Drr
    L-Drr is offline
    Forum Contributor
    Join Date
    08-02-2013
    Location
    Wageningen, The Netherlands
    MS-Off Ver
    365
    Posts
    495

    Re: Multiple (Some Exact/ Some Not) Criteria Lookup

    The second column you added can be avoided, by altering the formulas in the first added column like this:

    =SQRT(({old formula})^2)
    When I say semicolon, u say comma!
    Reply With QuoteRegister To Reply
  4. 08-07-2013, 05:36 AM #4
    RobertMika
    RobertMika is offline
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Multiple (Some Exact/ Some Not) Criteria Lookup

    Confirm Control+shift+Enter
    =INDEX(C$2:C$100,MATCH(MIN(IF($B$2:$B$100=G2,IF($A$2:$A$100=F2,ABS($D$2:$D$100-H2)))),IF($A$2:$A$100=F2,IF($B$2:$B$100=G2,ABS($D$2:$D$100-H2))),0))
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk
    Reply With QuoteRegister To Reply
  5. 08-07-2013, 06:45 AM #5
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: Multiple (Some Exact/ Some Not) Criteria Lookup

    Try this array formula in I2.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 08-07-2013, 04:17 PM #6
    stats90
    stats90 is offline
    Registered User
    Join Date
    07-26-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Multiple (Some Exact/ Some Not) Criteria Lookup

    Robert,

    This works well for the given spreadsheet, which is a mockup, since my true workbook has ~100,000 records.

    So in actuality I have 2 workbooks which I am trying to reference between (one is called customer list and has customer ID numbers). When I include all the referenced cells in the formula on the SAME worksheet, the formula works fine. When I reference any cell from another workbook, the formula returns N/A error. Because of the large data sets, I need to be able to reference the second workbook. All related cells are congruent format types (ie numbers are numbers, text are text). Any ideas?

    Thanks!

    Example:
    1) Cell referenced on SAME worksheet --> DOES work.
    {=INDEX(E1277:E1300,MATCH(MIN(IF(B1277:B1300=B1278,IF(D1277:D1300=D1277,ABS(J1277:J1300-M1279)))),IF(D1277:D1300=D1277,IF(B1277:B1300=B1277,ABS(J1277:J1300-M1279))),0))}

    2) Cell referenced on DIFFERENT worksheet: Does NOT work.
    {=INDEX(E1277:E1300,MATCH(MIN(IF(B1277:B1300='[customerlist.xlsx]Sheet1'!$C$229,IF(D1277:D1300=D1277,ABS(J1277:J1300-M1279)))),IF(D1277:D1300=D1277,IF(B1277:B1300=B1277,ABS(J1277:J1300-M1279))),0))}

    3) Actual value placed in formula: Does NOT work.
    {=INDEX(E1277:E1300,MATCH(MIN(IF(B1277:B1300=16381820,IF(D1277:D1300=D1277,ABS(J1277:J1300-M1279)))),IF(D1277:D1300=D1277,IF(B1277:B1300=B1277,ABS(J1277:J1300-M1279))),0))}

    4) Actual value placed in formula in double quotes: DOES Work.
    {=INDEX(E1277:E1300,MATCH(MIN(IF(B1277:B1300="16381820",IF(D1277:D1300=D1277,ABS(J1277:J1300-M1279)))),IF(D1277:D1300=D1277,IF(B1277:B1300=B1277,ABS(J1277:J1300-M1279))),0))}
    Last edited by stats90; 08-07-2013 at 04:19 PM.
    Reply With QuoteRegister To Reply
  7. 08-07-2013, 04:21 PM #7
    RobertMika
    RobertMika is offline
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Multiple (Some Exact/ Some Not) Criteria Lookup

    If option 4 works that means for me that this
    '[customerlist.xlsx]Sheet1'!$C$229
    returns numerical value
    Make sure that your ranges and the criteria are in the same format : Whatrver Text or Numerical but they are the same.
    Reply With QuoteRegister To Reply
  8. 08-07-2013, 04:47 PM #8
    stats90
    stats90 is offline
    Registered User
    Join Date
    07-26-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Multiple (Some Exact/ Some Not) Criteria Lookup

    Apparently, the numbers had been exported from a database and were stored as text which I missed (despite changing their format to "number"). I think everything will work now that I have "converted text to number" in the contextual error pop-up. Thanks so much.
    Reply With QuoteRegister To Reply
  9. 08-07-2013, 05:02 PM #9
    RobertMika
    RobertMika is offline
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Multiple (Some Exact/ Some Not) Criteria Lookup

    You are welcome.
    Please press the star under my avatar to add reputation
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Multiple criteria lookup - First match :exact" and second match "Closest"
    By mweichorn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2013, 12:14 PM
  2. [SOLVED] Multiple Exact Value Lookup and Sum Formula
    By nucky in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-10-2013, 12:10 PM
  3. LOOKUP Having one Exact matching criteria and one between 2 date ranges.
    By Grahame Hamilton in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-24-2012, 06:28 AM
  4. Two criteria lookup - exact & closest match
    By kapucino in forum Excel General
    Replies: 3
    Last Post: 01-28-2012, 01:19 PM
  5. Excel 2007 : Sumifs formula lookup multiple criteria in criteria range
    By matthews311 in forum Excel General
    Replies: 1
    Last Post: 05-16-2011, 05:00 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1