+ Reply to Thread
Results 1 to 6 of 6

Lookup and return value if adjacent cell is blank

  1. #1
    Registered User
    Join Date
    10-01-2020
    Location
    London, England
    MS-Off Ver
    365
    Posts
    78

    Lookup and return value if adjacent cell is blank

    Hi Community,

    I need to ability to create a lookup that returns the values of a cell if another cell value is blank in the same row.

    The attached sheet shows the information I have (A-G), and the outcome I require (I-O).
    Please Login or Register  to view this content.
    Then have somewhere, a
    Please Login or Register  to view this content.
    If it is possible to function, I realise the values returned for the first blank cell will all be the same, but this isn't a problem as I will need to manually go through each to check other data.
    Then I can add the allocation, which should then automatically trigger the references to change, as that will no longer be the first blank cell.

    I thought it wouldn't have been so difficult to figure it out, but I'm stumped.

    Thanks.

  2. #2
    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
    79,369

    Re: Lookup and return value if adjacent cell is blank

    See if this does what you want:

    =LOOKUP(2,1/(($F$2:$F$20=J2)*($G$2:$G$20="")),$E$2:$E$20)
    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.

  3. #3
    Registered User
    Join Date
    10-01-2020
    Location
    London, England
    MS-Off Ver
    365
    Posts
    78

    Re: Lookup and return value if adjacent cell is blank

    Hi AliGW,

    Thanks for the very fast reply.
    That works well. Is it possible it can return the first value from top down?
    Adding that formula to the sheet returned the last value for each of those.

  4. #4
    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
    79,369

    Re: Lookup and return value if adjacent cell is blank

    Oops!

    Try this:

    =INDEX($E$2:$E$20,MATCH(1,($F$2:$F$20=J2)*($G$2:$G$20=""),0))

  5. #5
    Registered User
    Join Date
    10-01-2020
    Location
    London, England
    MS-Off Ver
    365
    Posts
    78

    Re: Lookup and return value if adjacent cell is blank

    Perfection!
    Exactly as required. Many thanks.

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

    Re: Lookup and return value if adjacent cell is blank

    Glad to help.

+ 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. Replies: 1
    Last Post: 09-18-2021, 01:29 PM
  2. Lookup value and return adjacent cell
    By natty76 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-24-2019, 11:32 AM
  3. [SOLVED] Return Value Under Blank Above Adjacent Cell
    By Skiptomylou in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-10-2018, 10:55 AM
  4. [SOLVED] Lookup Next Blank Cell and Return Adjacent Cell
    By Ollie7957 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-29-2016, 05:02 AM
  5. lookup name and return value in adjacent cell
    By jaffacakes in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-25-2016, 05:46 AM
  6. Replies: 4
    Last Post: 05-15-2014, 09:32 AM
  7. [SOLVED] return value in adjacent cell or one above if that is blank.
    By Mikeytres in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-29-2012, 03:16 AM

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