+ Reply to Thread
Results 1 to 6 of 6

Lookup function with with three conditions. Two are fixed and the third is variable and c

  1. #1
    Registered User
    Join Date
    11-24-2012
    Location
    Annapolis, Maryland
    MS-Off Ver
    Excel 2010
    Posts
    36

    Lookup function with with three conditions. Two are fixed and the third is variable and c

    Good Afternoon:

    See attachment.

    Step One: TWO Cell Exact Match. Seek formula in yellow highlighted cells in Table 1 column A and B items for an exact match to items within Table 2 column I and J.
    Step Two: Conditional Lookup. Then enter soonest the time from column K exceeding or equalling column C time but by not more than one minute.
    Use the N:7 Cell as I may need to change the time number from time to time.
    Enter Zero (0) if there is no match meeting these conditions.

    Any way to do this without decending sort, or with standard non array formula?

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Lookup function with with three conditions. Two are fixed and the third is variable a

    Hi winship,

    why your manual result is 0:00:00 in D13 ? According to me, it should be 8:30:17. Please check. Thanks.

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Lookup function with with three conditions. Two are fixed and the third is variable a

    hi winship, spent quite a bit of time in this to make it non-array. try this in E13:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    like dilipandey, i have the same question. 8:12:31 AM is more than 1 minute from 8:11:30 AM & shouldnt be shown

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    11-24-2012
    Location
    Annapolis, Maryland
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Lookup function with with three conditions. Two are fixed and the third is variable a

    Good Morning:

    With respect to D13 being 0:00:00. The value in C13 is 14:29:00. Visual displays no value equal to or exceeding 14:29:00 and less than one minute within table 2 column J. 8:30:17 has a match for columns H and I, but the times are less than 14:29:00.

    Benishiryo has found what is a visual scanning error in D27. My template posts a number (08:12:31) that is 00:01:01 (greater than one minute) over 08:11:30. My regrets and that's why I need you guys!

    I will punch the benishiryo formula into my original for proofing. It may be that an array would be less complicated. Still learning about array rules as data changes, cell copy and macro use. Maybe it's ok?Maybe not a problem.

    Thanks again.

  5. #5
    Registered User
    Join Date
    11-24-2012
    Location
    Annapolis, Maryland
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Lookup function with with three conditions. Two are fixed and the third is variable a

    Good Morning:

    With respect to D13 being 0:00:00. The value in C13 is 14:29:00. Visual displays no value equal to or exceeding 14:29:00 and less than one minute within table 2 column J. 8:30:17 has a match for columns H and I, but the times are less than 14:29:00.

    Benishiryo has found what is a visual scanning error in D27. My template posts a number (08:12:31) that is 00:01:01 (greater than one minute) over 08:11:30. My regrets and that's why I need you guys!

    I will punch the benishiryo formula into my original for proofing. It may be that an array would be less complicated. Still learning about array rules as data changes, cell copy and macro use. Maybe it's ok?Maybe not a problem.

    Thanks again.

  6. #6
    Registered User
    Join Date
    11-24-2012
    Location
    Annapolis, Maryland
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Lookup function with with three conditions. Two are fixed and the third is variable a

    Formula works perfectly. Even found another visually generated error in row 28, row D. I had entered 08:30:17. The correct answer provided by formula is 08:29:39.

    Just excellant. Thank you so very much. I will mark this as solved, and you solved it despite my errors.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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