+ Reply to Thread
Results 1 to 9 of 9

VLOOKUP to Return Value if date falls between two dates

  1. #1
    Registered User
    Join Date
    12-22-2015
    Location
    Huntsville, AL
    MS-Off Ver
    2007
    Posts
    92

    Question VLOOKUP to Return Value if date falls between two dates

    Good Morning group,

    I feel like I must be missing something obvious here but I seem to be overlooking it. I thought this would be pretty simple. I've tried to find a solution on the web and in this forum with no luck.

    I have a column (F) in a Table that holds a date in each cell (all formatted as "short date").

    I have a column (I) in that same Table that I would like to show in which quarter that date falls.

    I have the following reference Table (named "Dates") on a separate sheet that looks like the attached jpg.

    I tried the following formula that I thought would work, but it keeps returning #N/A: =VLOOKUP(F2,Dates,3,FALSE)

    I don't know what I'm missing.

    So if anyone can either show me what I'm missing here or give me an alternate solution, it would be great!
    Attached Images Attached Images

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: VLOOKUP to Return Value if date falls between two dates

    See if this does what you want:

    =VLOOKUP(F2,Dates,3)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Valued Forum Contributor
    Join Date
    05-11-2013
    Location
    Wales
    MS-Off Ver
    Excel 2010
    Posts
    586

    Re: VLOOKUP to Return Value if date falls between two dates

    Hi,

    Are you able attach a copy of your file?

    peterrc

  4. #4
    Registered User
    Join Date
    12-22-2015
    Location
    Huntsville, AL
    MS-Off Ver
    2007
    Posts
    92

    Re: VLOOKUP to Return Value if date falls between two dates

    Tony,

    That returns #N/A as well.

    Petrrc,

    I've attached a cleaned up version (I had to remove several sheets for proprietary reasons)



    I have a feeling that it's something really simple that I happen to be overlooking.
    Attached Files Attached Files

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: VLOOKUP to Return Value if date falls between two dates

    Your dates in column F are not true Excel dates, they're TEXT strings that look like dates. The LEFT function returns TEXT values.

    To convert them into true Excel dates change the formula in F2 to:

    =--LEFT(D2,10)

    And change the formula in I2 to:

    =VLOOKUP(F2,Dates,3)

  6. #6
    Registered User
    Join Date
    12-22-2015
    Location
    Huntsville, AL
    MS-Off Ver
    2007
    Posts
    92

    Re: VLOOKUP to Return Value if date falls between two dates

    Tony,

    Very nice! That works great.

    May I ask what the -- does in front of the LEFT function? I've never seen that before.

  7. #7
    Registered User
    Join Date
    12-22-2015
    Location
    Huntsville, AL
    MS-Off Ver
    2007
    Posts
    92

    Re: VLOOKUP to Return Value if date falls between two dates

    Tony,

    Very nice! That works great.

    May I ask what the -- does in front of the LEFT function? I've never seen that before.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: VLOOKUP to Return Value if date falls between two dates

    The -- is known as double unary minus.

    It will convert text numbers and Boolean values into numeric values.


    --"5" = 5
    --TRUE = 1
    --FALSE = 0

    In your application the LEFT function returned a TEXT string that just happened to look like a date.

    The double unary minus can convert TEXT dates into true numeric dates.

    --"6/23/2016" = 6/23/2016 (formatted as Date)

  9. #9
    Registered User
    Join Date
    12-22-2015
    Location
    Huntsville, AL
    MS-Off Ver
    2007
    Posts
    92

    Re: VLOOKUP to Return Value if date falls between two dates

    Tony,

    Very nice! That works great.

    May I ask what the -- does in front of the LEFT function? I've never seen that before.

+ 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. VLOOKUP to return value if date falls between two dates
    By Cdubisms in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-23-2016, 11:49 AM
  2. VLOOKUP to return value if date falls between two dates
    By Cdubisms in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-23-2016, 11:48 AM
  3. [SOLVED] Index Match-return an amount if a date falls between two dates
    By HRpersonnel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-09-2016, 12:06 AM
  4. HELP: Return a value with a date that falls between two other dates
    By hionman in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-18-2014, 08:01 AM
  5. [SOLVED] Looking for formula to return a date that falls between 2 dates
    By cobo1981 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-13-2014, 01:12 PM
  6. Replies: 3
    Last Post: 10-13-2012, 08:09 PM
  7. [SOLVED] Return value in X if date in Y falls between two dates - Help!
    By nephilim3uk in forum Excel General
    Replies: 4
    Last Post: 07-08-2012, 10:13 AM

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