+ Reply to Thread
Results 1 to 4 of 4

VLOOKUP to return a start date if it falls between two dates otherwise leave blank

  1. 10-11-2012, 12:25 PM #1
    rnomis
    rnomis is offline
    Registered User
    Join Date
    09-07-2011
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    23

    Question VLOOKUP to return a start date if it falls between two dates otherwise leave blank

    I have a sheet where I need to return a start date if the date is between certain dates. Each record has a start date but I want to display the date in another sheet only if it is between September 1st 2012 and 31st August 2013
    In one sheet (Data) I have the two dates:

    B8 = 1st September 2012
    C8 = 31st August 2013

    In another sheet (S360) I have the start dates (column AV) along with the rest of the static data including the ID in Column A which I update weekly.

    In my master sheet, Column A has the ID for the client and the start date is returned in column X

    I have the following which works:

    =IF(AND('S360'!AV2>Data!$B$8,'S360'!AV2<Data!$C$8),'S360'!AV2, "")

    However, There is a lot of other data which can be filtered and/or sorted in a number of ways with VLOOKUPs. For example:

    =IF(ISNA(VLOOKUP(A2,'S360'!$A$2:$AL$2687,6,FALSE)),"",VLOOKUP(A2,'S360'!$A$2:$AL$2687,6,FALSE)) is the Area location of the client and I may filter this alphabetically or by area. When I do this, the data throws up an Inconsistent Formula warning. I am not sure if this is actually a problem but I think it could be.

    How can I add a VLOOKUP to the formula so it always shows the correct data?

    Thanks in anticipation.

    Simon
    Reply With QuoteRegister To Reply
  2. 10-13-2012, 06:19 PM #2
    Jacc
    Jacc is offline
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,844

    Re: VLOOKUP to return a start date if it falls between two dates otherwise leave blank

    Your description of the problem is not bad but a sample workbook would be so much easier.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
    Reply With QuoteRegister To Reply
  3. 10-13-2012, 06:38 PM #3
    FDibbins
    FDibbins is offline
    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,929

    Re: VLOOKUP to return a start date if it falls between two dates otherwise leave blank

    have you tried using index/match instead of vlookup? you can acutally reference row and column "headings with this, which makes it better than vlookup in some situations
    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
    Reply With QuoteRegister To Reply
  4. 10-13-2012, 08:01 PM #4
    rnomis
    rnomis is offline
    Registered User
    Join Date
    09-07-2011
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: VLOOKUP to return a start date if it falls between two dates otherwise leave blank

    OK, I have tried the MATCH and INDEX as suggested but I think I still have a problem when I change the order of the columns. The two columns that I think are wrong are I & J which I think are referencing the wrong data if column D is reordered alphabetically or column F is reordered by date order.

    It would be great to get this working as I & J are important for my companies reporting.
    I attach a small sample from the total data as the full spread sheet comes out at 14Mb!

    Thanks for your help so far.
    Attached Files Attached Files
    Last edited by rnomis; 10-13-2012 at 08:04 PM. Reason: add attachment
    Reply With QuoteRegister To Reply
  5. 10-13-2012, 08:09 PM #5
    rnomis
    rnomis is offline
    Registered User
    Join Date
    09-07-2011
    Location
    Brighton, England
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: VLOOKUP to return a start date if it falls between two dates otherwise leave blank

    had a problem with attachments but sorted
    Last edited by rnomis; 10-13-2012 at 08:13 PM.
    Reply With QuoteRegister To Reply
  6. 10-13-2012, 08:42 PM #6
    FDibbins
    FDibbins is offline
    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,929

    Re: VLOOKUP to return a start date if it falls between two dates otherwise leave blank

    thats a pretty complex file you have there and im having a hard time following it through. However, try this in I2 and let me know if thats what you want? (if it it, you were real close)

    =INDEX(Data!$A$27:$B$31,MATCH('S360'!AY2,Data!$B$27:$B$31),2)

    2007 has a few new functions. 1 of them is iferror, using this, you can simplify
    =IF(ISNA(VLOOKUP(A2,'S360'!$A$2:$AA$2687,5,FALSE)),"",VLOOKUP(A2,'S360'!$A$2:$AA$2687,5,FALSE))
    to
    =IFERROR(VLOOKUP(A2,'S360'!$A$2:$AA$2687,5,FALSE),"")
    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)

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