Closed Thread
Results 1 to 11 of 11

XLOOKUP for dates avoid 1/0/1900 for blank cells

  1. 11-18-2020, 10:16 AM #1
    Webbers
    Webbers is offline
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    XLOOKUP for dates avoid 1/0/1900 for blank cells

    Hi all!

    I am using the XLOOKUP function, and I love it, except now! My data value is a date, and there are many BLANK cells within the data. I have searched through multiple examples of XLOOKUP, and have not found anything that references dates as the data and a dataset that contains blanks. I have created a sample dataset, containing only the relevant information, 2 tabs, one with the data (export), and the other is the report I am building.

    Column R of Sheet1 contains the formula

    =XLOOKUP(A2,data!$A$2:$A$100,data!$E$2:$E$100,"-")

    The result from a blank cell (in column E) on the data tab is "1/0/1900". I need this to display "-" in the cell if there cell on the data tab is blank. I have tried everything I can think of, but nothing works. Thanks in advance.
    ~*~ Sherry ~*~
    Poinciana, FL
    Reply With QuoteRegister To Reply
  2. 11-18-2020, 10:17 AM #2
    AliGW
    AliGW is offline
    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
    81,224

    Re: XLOOKUP for dates avoid 1/0/1900 for blank cells

    Try this:

    =IFERROR(1/(1/XLOOKUP(A2,data!$A$2:$A$100,data!$E$2:$E$100,"-")),"")
    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.
    Reply With QuoteRegister To Reply
  3. 11-18-2020, 10:21 AM #3
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,097

    Re: XLOOKUP for dates avoid 1/0/1900 for blank cells

    If the values in E are dates try
    =IFERROR(1/(1/XLOOKUP(A2,Data!$A$2:$A$100,Data!$E$2:$E$100,"-")),"-")

    Beaten 2it
    Reply With QuoteRegister To Reply
  4. 11-18-2020, 10:44 AM #4
    Webbers
    Webbers is offline
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: XLOOKUP for dates avoid 1/0/1900 for blank cells

    @AliGW,

    Perfect! Using dates is rather common, and having blanks is rather normal in the real world, I am so happy I have friends like you! Thanks!!!!
    Reply With QuoteRegister To Reply
  5. 11-18-2020, 10:46 AM #5
    AliGW
    AliGW is offline
    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
    81,224

    Re: XLOOKUP for dates avoid 1/0/1900 for blank cells

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  6. 11-18-2020, 10:46 AM #6
    Webbers
    Webbers is offline
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: XLOOKUP for dates avoid 1/0/1900 for blank cells

    @Fluff13,

    You ALWAYShave the right answer for me, and FAST too! I am proud to call you my friend! Creating a report with errors like these is not acceptable to me. You rock!!!!!
    Reply With QuoteRegister To Reply
  7. 11-18-2020, 10:53 AM #7
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,213

    Re: XLOOKUP for dates avoid 1/0/1900 for blank cells

    Please try custom format

    M/D/YYYY;;-
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  8. 11-18-2020, 11:00 AM #8
    Webbers
    Webbers is offline
    Forum Contributor Webbers's Avatar
    Join Date
    09-06-2005
    Location
    Poinciana, FL
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    337

    Re: XLOOKUP for dates avoid 1/0/1900 for blank cells

    Sorry AliGW. I had not gotten to that point... I got called into a meeting before I could mark it. Done now, and tysvm!!! Keeping this special variation in my notes!!!!
    Reply With QuoteRegister To Reply
  9. 11-18-2020, 11:02 AM #9
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,097

    Re: XLOOKUP for dates avoid 1/0/1900 for blank cells

    Glad to help & thanks for the feedback.
    Reply With QuoteRegister To Reply
  10. 03-23-2022, 07:48 PM #10
    MrsGG
    MrsGG is offline
    Registered User
    Join Date
    03-23-2022
    Location
    Essex
    MS-Off Ver
    2016
    Posts
    1

    Re: XLOOKUP for dates avoid 1/0/1900 for blank cells

    I know this is an old thread but it has really helped me out, but im struggling (always do with multiple statements) - can you help please?

    my formula is this:

    =XLOOKUP($B207,'[Book1.xlsx]ALL'!$F$4:$ABT$4,'[Book1.xlsx]ALL'!$F$10:$ABT$10,XLOOKUP($B207,'[Book2.xlsx]All'!$G$4:$ABT$4,'[Book2.xlsx]All'!$G$10:$ABT$10,XLOOKUP($B207,'[Book3.xlsx]All'!$J$4:$ABT$4,'[Book3.xlsx]All'!$J$10:$ABT$10,XLOOKUP($B207,'[Book4.xlsx]ALL'!$F$4:$ABT$4,'[Book4.xlsx]ALL'!$F$10:$ABT$10))))

    is it possible to include an if error here too? Im getting the 1900 date when the fields are not populated

    thanks in advance
    Reply With QuoteRegister To Reply
  11. 03-24-2022, 02:56 AM #11
    AliGW
    AliGW is offline
    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
    81,224

    Re: XLOOKUP for dates avoid 1/0/1900 for blank cells

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Reply With QuoteRegister To Reply
Closed 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. Xlookup - return blank IF contacts certain text
    By Tally04 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-23-2020, 09:44 AM
  2. Xlookup blank cells - doesn't return error!
    By Jedab in forum Office 365
    Replies: 3
    Last Post: 02-11-2020, 06:05 PM
  3. [SOLVED] Iferror empty dates blank instead 0.01.1900
    By Pulleritz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-31-2019, 02:54 AM
  4. Average non-contiguous cells, ignore blank cells and avoid #DIV/0!
    By Davdef in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 10-06-2018, 10:03 AM
  5. Vlookup returning 1/0/1900 for blank cells
    By ashley72788 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-15-2015, 03:17 PM
  6. Indexing dates, blank on one sheet displaying as 00/01/1900
    By Gooford in forum Excel General
    Replies: 4
    Last Post: 02-12-2010, 07:46 AM
  7. how can avoid considering of blank cells in IF function
    By Lika in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-12-2006, 05: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1