+ Reply to Thread
Results 1 to 2 of 2

vlookup returns 1/0/1900 search two sheets

  1. #1
    Registered User
    Join Date
    10-10-2011
    Location
    Arizona, USA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Angry vlookup returns 1/0/1900 search two sheets

    I am having trouble getting the following code to return a Blank when no date occurs in the two locations it looks

    =IFERROR(VLOOKUP($A68,Sheet1!$F$1:$DC$2584,20,FALSE),VLOOKUP($A68,Sheet2!$F$1:$DC$2584,20,FALSE))

    Currently if it find a matching number in $A68 on Sheet1 or Sheet2 it returns the Date from 20 over from $F$1 in my spread sheet. However when there is no date and it is blank, I receive 1/0/1900. I am using this sheet and date to fill another forums. Everytime I find a way to write the code to return a blank, I loose the ability to search in both Sheet1 and Sheet2.

    Can someone help me out here.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: vlookup returns 1/0/1900 search two sheets

    1/0/1900 is basically a 0 formatted as a Date (1 = Jan 1 1900)
    However, 0 is NOT an error, therefor will not be trapped by the IFERROR function.

    Try this assuming the expected return value is a Date.

    =IFERROR(1/(1/IFERROR(VLOOKUP($A68,Sheet1!$F$1:$DC$2584,20,FALSE),VLOOKUP($A68,Sheet2!$F$1:$DC$2584,20,FALSE))),"")

+ 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. MIN and MAX with Dates returns 00/01/1900
    By Motox in forum Excel General
    Replies: 3
    Last Post: 01-09-2015, 01:48 AM
  2. Vlookup returns 1/0/1900?
    By CSPENCER in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-17-2014, 03:11 PM
  3. VLOOKUP returns error if value is not in all sheets?
    By MJ10 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-21-2013, 06:43 PM
  4. how to bring all vlookup returns even with duplicate vlookup search keys
    By NYC4LIFE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-06-2013, 04:53 PM
  5. VLOOKUP returns #NA! on wildcard search
    By theromex in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-12-2013, 10:10 PM
  6. [SOLVED] VLOOKUP function returns 1/0/1900
    By kpodonnell in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-21-2012, 08:50 PM
  7. [SOLVED] WEEKNUM in pivot returns 1900-01-31
    By [email protected] in forum Excel General
    Replies: 5
    Last Post: 08-07-2006, 07:30 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