+ Reply to Thread
Results 1 to 7 of 7

Lokup Function help needed

  1. #1
    Registered User
    Join Date
    04-11-2013
    Location
    Nebraska, United States
    MS-Off Ver
    Excel 2007
    Posts
    3

    Lokup Function help needed

    Sample.xlsxHi all,

    Need some help on a excel issue with Lookup (possibly not using right function right now but that is what we started with).

    Workshet A, we have data in columns A - D and then a date in column E that is the due date, and then the columns that we want to determine are column F which we want the release date and column G the release number.

    Worksheet B, we have column A with the release date and then column B with the release number.

    We want the due date to be less than or equal to the release date. Examples:
    due date of 04/01/13 we would want the first row in worksheet B with release date of 04/15/13 (col. F) and release number #1 (Col. G)
    due date of 04/30/13 we would want the second row in worksheet B with release date of 05/15/13 (col. F) and release number #2 (Col. G)
    due date of 05/16/13 we would want the third row in worksheet B with release date of 06/15/13 (col. F) and release number #3 (Col. G)


    Right now we have this formula
    =IFERROR(LOOKUP(DATEVALUE(A4),DATEVALUE(Releases!$A$2:$A$100),Releases!$B$2:$B$100),"Before First Listed Date")

    but on a due date of 05/01/13 it is returning release date of 04/15/13 and release # of 1.

    Any help would be greatly appreciated!! Thank you!!
    Last edited by webba05; 04-15-2013 at 09:56 AM.

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

    Re: Lokup Function help needed

    Hi webba05,

    welcome to the forum.
    Please upload a sample workbook along with your expected output. Thanks.


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

  3. #3
    Registered User
    Join Date
    03-24-2013
    Location
    Lahore, Pakistan
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Lokup Function help needed

    Hi
    can you tell me what kind of data is in columns A-D?

  4. #4
    Registered User
    Join Date
    04-11-2013
    Location
    Nebraska, United States
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Lokup Function help needed

    Thank you dilipandey, I updated the first post with the sample. I took the data out of the columns before it because it is just text that does not really have anything to do with the dates. Thanks for looking at this!!

  5. #5
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Lokup Function help needed

    Hello,

    I have to used array for it to work, and my formula is
    =INDEX(Releases!$A$2:$A$99,SMALL(IF(Releases!$A$2:$A$99>=$A2,ROW(Releases!$A$2:$A$99)-ROW($A$2)+1),1))
    When you enter the formula, you have to use ctrl-shift-enter for it to work, because it's an array formula.
    Similarly, for the Release column,
    =INDEX(Releases!$B$2:$B$99,SMALL(IF(Releases!$A$2:$A$99>=$A2,ROW(Releases!$A$2:$A$99)-ROW($A$2)+1),1))
    But the funny part is, for the row with Due Date of 03/01/2013, it returns the value of 04/15/2013.
    Please let me know if this is the kind of results you are looking for, meanwhile I'll try to work around lookup with this.

    Regards
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Lokup Function help needed

    both sets of "dates" you are using are text, not dates (as you already know), so try this....

    1st, add a helper column to your "dates" on sheet2 (I used C) and convert your "text" to dates with...
    =A2*1

    then on sheet1, use this, copied down...
    =INDEX(Releases!$B$2:$B$8,MATCH(A14*1,Releases!$C$2:$C$8,1)+1,1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Registered User
    Join Date
    04-11-2013
    Location
    Nebraska, United States
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Lokup Function help needed

    That worked!! Thank you so much!! That is really going to help us!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1