+ Reply to Thread
Results 1 to 8 of 8

VLOOKUP: Date vs. Date Issue

  1. 01-31-2015, 04:35 PM #1
    civitarl
    civitarl is offline
    Registered User
    Join Date
    01-31-2015
    Location
    Boston
    MS-Off Ver
    2012
    Posts
    5

    VLOOKUP: Date vs. Date Issue

    Hello all and thanks in advance!

    I'm losing it here... I want to do a VLOOKUP in the attached file where I am looking up the date in column H in column AD to return data in column AH. I have the following function put in =VLOOKUP(H4,$AD$5:$AH$3790,5,FALSE) however, as you can see I get NA's for everything. I've tried just about everything....changing number formats, converting to text, concatenating...) Please Help...Desperate...need a shower.

    Let me know the steps you took because I will need to do it on a larger dataset.

    Cheers,
    Louis

    Options Data v3.xlsx
    Reply With QuoteRegister To Reply
  2. 01-31-2015, 04:43 PM #2
    BadlySpelledBuoy
    BadlySpelledBuoy is offline
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: VLOOKUP: Date vs. Date Issue

    Notice how the dates in column H are to the left and the ones in column AD are to the right?
    This is a tell tale sign that the values in AD are recognised as dates and the ones in H are not, and the reason why you're lookup isn't working.
    Reply With QuoteRegister To Reply
  3. 01-31-2015, 04:44 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: Date vs. Date Issue

    Hi, welcome to the forum

    Your dates in H are not really dates, they are text that looks like a date. Try this variation...

    =VLOOKUP(H4*1,$AD$5:$AH$3626,5,FALSE)
    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. 01-31-2015, 04:45 PM #4
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VLOOKUP: Date vs. Date Issue

    Hi,

    Column H are just strings that look like dates whereas column AD are dates proper, i.e. numbers
    First convert the strings to numbers. e.g. in AC2 copied down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Then use AC in your VLOOKUP
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  5. 01-31-2015, 04:59 PM #5
    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: Date vs. Date Issue

    If you need/want to convert those text-dates to real dates, highlight the entire range, selectthe Data Tab/click Text2Columns, click next/next/next/check Date/OK
    Reply With QuoteRegister To Reply
  6. 01-31-2015, 05:15 PM #6
    civitarl
    civitarl is offline
    Registered User
    Join Date
    01-31-2015
    Location
    Boston
    MS-Off Ver
    2012
    Posts
    5

    Re: VLOOKUP: Date vs. Date Issue

    Thank you so much! Worked like a charm.

    Cheers,
    Louis
    Reply With QuoteRegister To Reply
  7. 01-31-2015, 05:16 PM #7
    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: Date vs. Date Issue

    Happy to help, thanks for the feedback
    Reply With QuoteRegister To Reply
  8. 01-31-2015, 07:16 PM #8
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VLOOKUP: Date vs. Date Issue

    Thanks for the rep and glad to have helped.
    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)

Similar Threads

  1. VLOOKUP issue using date as criterion
    By dobbinuk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-24-2014, 08:07 AM
  2. VLOOKUP date issue (formatting?)
    By aaronwill in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-22-2014, 01:21 PM
  3. [SOLVED] Issue with date format when comparing two date values (I'm in Australia)
    By aaron.irvine in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-08-2013, 01:13 AM
  4. [SOLVED] issue finding a date in vlookup
    By Araise in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 02-17-2013, 07:17 PM
  5. Date Picker Date Format issue
    By AndyE in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-21-2012, 06:43 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1