+ Reply to Thread
Results 1 to 6 of 6

need VLOOKUP to ignore blanks and carry on looking until it finds something

  1. #1
    Forum Contributor
    Join Date
    12-11-2013
    Location
    St Moritz, GR, Switzerland
    MS-Off Ver
    Excel 2010
    Posts
    187

    need VLOOKUP to ignore blanks and carry on looking until it finds something

    =VLOOKUP(A1,C:D,2,false)

    The value in A1 appears several times in columns C. Sometimes D has a value, sometimes it's blank.

    Can I make VLOOKUP keep on looking until it finds a value, and only return blank if they're all blank? Demo attached.
    Attached Files Attached Files
    Last edited by johnandrews; 12-03-2014 at 12:21 PM.

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: need VLOOKUP to ignore blanks and carry on looking until it finds something

    VLOOKUP will return the first value for the first match it hits within a column. That, in your example, is D1, since C1 is the first "John" it hits. Apart from altering the formula, adding in a last name for your name list could help improve your searches.

    However, if you aren't stuck on using VLOOKUP, try this:

    Please Login or Register  to view this content.
    Needs to be confirmed with CTRL+SHIFT+ENTER

    Hope this helps!
    Last edited by mcmahobt; 12-03-2014 at 12:35 PM.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,490

    Re: need VLOOKUP to ignore blanks and carry on looking until it finds something

    This MIGHT be what you want...

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    It returns the value in Col D corresponding to the LAST match for John in Column C...
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  4. #4
    Forum Contributor
    Join Date
    12-11-2013
    Location
    St Moritz, GR, Switzerland
    MS-Off Ver
    Excel 2010
    Posts
    187

    Re: need VLOOKUP to ignore blanks and carry on looking until it finds something

    It does help. Index function it is.

  5. #5
    Registered User
    Join Date
    03-15-2019
    Location
    sloveni
    MS-Off Ver
    2016
    Posts
    27

    Re: need VLOOKUP to ignore blanks and carry on looking until it finds something

    Hello,

    How do I write the formula so that if the
    lookup index or vlookup (cell) is blank, go to the next cell and use that value
    if that one is also blank go to the next cell and use that value...?
    Attached Files Attached Files

  6. #6
    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,969

    Re: need VLOOKUP to ignore blanks and carry on looking until it finds something

    Quote Originally Posted by pernekde View Post
    Hello,

    How do I write the formula so that if the
    lookup index or vlookup (cell) is blank, go to the next cell and use that value
    if that one is also blank go to the next cell and use that value...?
    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
    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 to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. If Statement, ignore blanks
    By shanikakbrown in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-12-2014, 05:34 PM
  2. [SOLVED] MIN/MAX IF To Ignore Blanks
    By splendidus in forum Excel General
    Replies: 2
    Last Post: 07-04-2012, 04:00 PM
  3. Ignore blanks using xldown etc
    By stankraj in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-26-2009, 05:10 PM
  4. [SOLVED] Loop that finds blanks, then subtotals values into different column
    By Bevy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-24-2006, 11:45 AM
  5. [SOLVED] USING IGNORE BLANKS IN FORMULA
    By Roger H. in forum Excel General
    Replies: 5
    Last Post: 04-06-2005, 12:06 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1