+ Reply to Thread
Results 1 to 6 of 6

VLOOKUP for a range of dates

  1. #1
    Registered User
    Join Date
    12-21-2014
    Location
    somewhere
    MS-Off Ver
    2010
    Posts
    36

    VLOOKUP for a range of dates

    Is there a way such that I can combine a range of dates in the one cell so that I can use it for vlookup?

    What I mean is something like this

    1/1/2014-31/1/2014 100
    1/2/2014-28/1/2014 96
    ...

    Afterwards, I would then like to look up the value corresponding to the date 15/1/2014

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,807

    Re: VLOOKUP for a range of dates

    Why would you want to use a range like that? If your date ranges are consecutive - and in early-to-latest order - vlookup should manage ok?

    What exactly are you trying to do?

    can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    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

  3. #3
    Registered User
    Join Date
    12-21-2014
    Location
    somewhere
    MS-Off Ver
    2010
    Posts
    36

    Re: VLOOKUP for a range of dates

    Here is a sample
    Attached Files Attached Files

  4. #4
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    2,261

    Re: VLOOKUP for a range of dates

    Perhaps something like this? Using array formula (means when you ENTERING this formula you need to press CTRL+SHIFT+ENTER button together, dont' ENTER alone)
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-25-2013
    Location
    Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: VLOOKUP for a range of dates

    Another solution is to place the start and end dates in separate columns. Since each start date is the day after the previous end date you can ignore the end date and use =VLOOKUP(F4,A1:C4,3,TRUE) - the TRUE (approximate match) means that it will select the row containing the highest date that is not greater than the one specified.
    I've attached an example.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    13,814

    Re: VLOOKUP for a range of dates

    @Skillsguy

    Although the solution in #4 of azumi works and provides the answer in the conditions of your question,

    I advice you to go for the offered solution in #5 of Larena.

    That formula is much better to understand and therefor easier to change.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ 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. [SOLVED] Vlookup on Dates and generating date range based upon inputs
    By Chardo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-14-2014, 05:13 PM
  2. Replies: 6
    Last Post: 09-04-2012, 10:35 AM
  3. Excel 2007 : Using VLOOKUP to find a range of dates
    By vjpemberton13 in forum Excel General
    Replies: 3
    Last Post: 04-18-2012, 02:50 PM
  4. Workday and Vlookup to select a range of holiday dates
    By vadius in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-29-2011, 10:28 AM
  5. vlookup over range of dates
    By Chuckers in forum Excel General
    Replies: 3
    Last Post: 02-02-2010, 10:18 PM

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