How to get vlookup to not lookup search value is blank

Hello -

I have several sheets that link together. The Client Profile is the "master sheet" were 85% of data is contained. As a new client is added, a client # is assigned to that person. Then on the next sheet - the Account Profile sheet, we enter the assigned client # and I have a simple Vlookup.

We ran across an issue were a client was entered on the Client Profile but no Client # was assigned - should be rare but it might happened. On the Account Profile, it started to pull that clients information into any row that had a Client # that was blank.

Screenshot of Client Profile that has Client number assigned

Screenshot of formula

Because Client #2 is blank - it kept pulling the one client profile that was not assigned a client #. What can I add to my vlookup that says if Client #2 or Entity #2 is blank, don't do the lookup?

Thanks in advance!


  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    edited 12/08/22

    @Christina Reid You can add IF and isblank to the beginning...

    =IF(isblank([Client # 2]@row),"", Your VLOOKUP formula here)

    If you want to include Entity # 2 in the If statement...

    =if(or(isblank([Client # 2]@row), isblank([Entity # 2]@row)), "", Your VLOOKUP formula here)

    See if that helps.

    **Also, VLOOKUPs are great but if someone moves one of your columns out of order, it could break. Try to learn Index/match instead. It gives the same result, but it will not break if your columns move.

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!