VLOOKUP IF "Column B" Is Blank

Options
A Rose
A Rose ✭✭✭✭✭
edited 06/04/21 in Formulas and Functions

Hi,

I'd like to do a vlookup If Column B is blank, than vlookup, if it contains any value, Copy that value here in this cell.

Is that possible?

Thank you!

Best Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓
    Options

    Assuming that Column A contains the common identifier in both your Source and your Target (current worksheet) then try the following...

    In Column C use the formula,

    =IF(ISBLANK([Column B]@row),IFERROR(VLOOKUP([Column A]@row, [Your_Source_Range],99, FALSE),""),[[Column B]@row])

    where 99 is the column in your Source range that might have the value you're looking for. "FALSE" in VLOOKUP() indicates that you don't need an exact match.

    When selecting the range from your source (defining the External Reference), ensure that the first column of the range contains the common identifier.

  • A Rose
    A Rose ✭✭✭✭✭
    Answer ✓
    Options

    Hi @TFC ,

    Small correction in Bold below.

    =IF(ISBLANK([Column B]@row),IFERROR(VLOOKUP([Column A]@row, [Your_Source_Range],99, FALSE),""),[Column B]@row)


    Thanks

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @TFC

    I hope you're well and safe!

    To add to A Rose's excellent advice/answer.

    Another way to write ISBLANK is = ""

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Answer ✓
    Options

    Assuming that Column A contains the common identifier in both your Source and your Target (current worksheet) then try the following...

    In Column C use the formula,

    =IF(ISBLANK([Column B]@row),IFERROR(VLOOKUP([Column A]@row, [Your_Source_Range],99, FALSE),""),[[Column B]@row])

    where 99 is the column in your Source range that might have the value you're looking for. "FALSE" in VLOOKUP() indicates that you don't need an exact match.

    When selecting the range from your source (defining the External Reference), ensure that the first column of the range contains the common identifier.

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    @TFC ,

    Thank you so much!

    You taught me a new thing "ISBLANK".

    Thank you! 😀

  • A Rose
    A Rose ✭✭✭✭✭
    Answer ✓
    Options

    Hi @TFC ,

    Small correction in Bold below.

    =IF(ISBLANK([Column B]@row),IFERROR(VLOOKUP([Column A]@row, [Your_Source_Range],99, FALSE),""),[Column B]@row)


    Thanks

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @TFC

    I hope you're well and safe!

    To add to A Rose's excellent advice/answer.

    Another way to write ISBLANK is = ""

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Kate Kelley
    Kate Kelley ✭✭✭✭
    Options

    Hi,

    I'm looking for the opposite of this I think, or something close to it. I have two sheets - one is the primary information and the second is a large sheet where people submit information on a form it fills in. On the primary information sheet, I would like two columns to check that people have submitted the form. In one column, if the initial person submits the form, it successfully checks a box. The second column (where this formula is needed), I'd like to do a Vlookup to see if the primary email is on the second sheet and if another field on that sheet is not blank, it would then check the box.

    I think the formula would start with the vlookup and if source range column is not blank, return a checkbox.

    Is this possible?

    Thanks!

    Kate

  • A Rose
    A Rose ✭✭✭✭✭
    Options

    Hi,

    I think this is if you want to use is not blank

    IF(Column1@row, <>"","Not Blank","")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!