How to get vlookup to not lookup search value is blank

Christina Reid

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

@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.