View Poll Results: POLL CLOSED

Voters
0. This poll is closed
  • xxx

    0 0%
  • xxx

    0 0%
  • xxx

    0 0%
Results 1 to 4 of 4

Xlookup blank cells - doesn't return error!

Threaded View

  1. #1
    Registered User
    Join Date
    02-11-2020
    Location
    Israel
    MS-Off Ver
    Office 365
    Posts
    1

    Xlookup blank cells - doesn't return error!

    I was excited to start using XLOOKUP, but am distraught to see that it "looks up" blank cells and 'matches' them to the first blank cell found in the lookup array!
    If you're like me an your lookup arrays are usually full-columns, which always have blank cells at the bottom, this is NOT GOOD!

    When one uses VLOOKUP, and the lookup cell is blank, it would return an error, which was great, since one could nest the vlookup formula within the 'iferror' formula like this,

    =iferror(vlookup(...),"")

    so if the lookup was blank the result would be blank. SO this iferror formula would address any non-found values or blanks, and if you forgot the iferror formula the 'results' would remind you with their #N/A values.

    Xlookup addresses non-found values within the formula itself ('if not found' parameter), which is nice, but since it matches blank lookup cells to blank cells in the lookup array, it will still need to be sandwhiched with another formula; I'm thinking:

    =if('lookupcell'="","",xlookup(...))

    which certianly detracts from the excitement. Also, if one forgot to nest a vlookup formula within an iferror formula, the error results would serve as a reminder. For Xlookup, howver, if one forgets to address blanks, it will likely generate clean-looking results that are not desired. For this reason I would prefer Microsoft change the xlookup to return error (or the not found parameter) if the lookup cell itself is blank. Anyone else agree? Any other suggestions or workarounds towards addressing my blank-blank match concern?

    xlookup of blank cell.JPG
    Attached Files Attached Files
    Last edited by Jedab; 02-11-2020 at 08:06 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. If Vlookup doesn't find any value, return blank cell
    By jacobmarsh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-16-2019, 11:10 AM
  2. [SOLVED] Cells in a range are all not blank, return maximum value (date), If 1 blank return 0
    By Matt1998 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-19-2016, 11:14 AM
  3. [SOLVED] formula doesn't return blank as it should
    By sipa in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 11-02-2015, 11:11 AM
  4. VBA Function doesn't return anything (not even an error)
    By seigna in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-05-2013, 06:19 AM
  5. An equals formula that doesn't return a blank cell
    By stanja in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-30-2009, 03:37 PM
  6. Replies: 3
    Last Post: 11-22-2007, 07:53 AM
  7. Excel doesn't think blank cells are not really blank?
    By leonnord in forum Excel General
    Replies: 3
    Last Post: 08-01-2007, 02:34 PM

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