+ Reply to Thread
Results 1 to 8 of 8

VLookUp and Dates

  1. #1
    Registered User
    Join Date
    02-13-2017
    Location
    Liverpool UK
    MS-Off Ver
    2013
    Posts
    2

    VLookUp and Dates

    Hi,

    I have a spreadsheet where I want to retrieve a date from one worksheet into another. I have set up the VLOOKUP and it keeps returning #NA. I have double checked that both columns in both worksheets are DATE format however I cannot get this to work. Any help is appreciated.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,419

    Re: VLookUp and Dates

    This is not a VBA or programming query, so I've moved it to the correct section.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,419

    Re: VLookUp and Dates

    You can't use VLOOKUP for a right to left lookup. Try this instead:

    =INDEX('Op-1'!$A$2:$A$9,MATCH('Op-2'!B2,'Op-1'!$B$2:$B$9,0),0)

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: VLookUp and Dates

    You can't look from right to left with vlookup alone. Since vlookup uses left most column in lookup range to lookup value.

    Alternate formula:
    =INDEX('Op-1'!$A$2:$A$9,MATCH(B2,'Op-1'!$B$2:$B$9,0))

    Or read method documented in link.
    http://chandoo.org/wp/2012/09/06/for...ensics-no-028/

  5. #5
    Forum Contributor
    Join Date
    02-13-2017
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    102

    Re: VLookUp and Dates

    In worksheet 'Op-1', put the 'Name' column before the 'date' column.
    U cant search 'backwards'. The value u search, is in the column behind the date you want to return.
    The value that you want to search has to be in the first column of the table range.

    Then use this formula: =VLOOKUP(B2;'Op-1'!A2:B9;2;FALSE)

    Ps: sorry for my bad English.

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: VLookUp and Dates

    Try this one for left lookup
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  7. #7
    Registered User
    Join Date
    02-13-2017
    Location
    Liverpool UK
    MS-Off Ver
    2013
    Posts
    2

    Re: VLookUp and Dates

    Apologies AliGW for posting in wrong area and thanks for moving to correct area.

    Thanks to all for helping me with this the Index way worked and I know get the idea you cant search backwards.

    Kam

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,419

    Re: VLookUp and Dates

    You are most welcome!

+ 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. Replies: 3
    Last Post: 11-25-2015, 10:58 AM
  2. [SOLVED] Vlookup dates between two dates and count the number of days
    By nishikanth in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-28-2013, 03:25 AM
  3. [SOLVED] Vlookup on dates
    By jswainson in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-08-2012, 01:51 PM
  4. [SOLVED] vlookup and dates
    By trobinson in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-25-2006, 09:15 AM
  5. dates and vlookup
    By grapes in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-02-2006, 05:25 PM
  6. VLookUp with Dates
    By bj in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 09:05 AM
  7. [SOLVED] VLookUp with Dates
    By o1darcie1o in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

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