Vlookup to return blank cell if nothing in range

[Haz]

Hi,

I have a Vlookup as this =if(g12="","",VLOOKUP(g12, NameAccno,2,0)). I would
like this function to return a blank cell if there is no entry in "name
accno" to match. at the moment it returns 0. I am using this as part of a
drop down selection on another cell.

also, Is it possible to make the above formula cell undeletable so it is
always there?

 

[Mike H]

perhaps

=IF(G12="","",IF(ISNA(VLOOKUP(G12, NameAccno,2,0)),"",VLOOKUP(G12,
NameAccno,2,0)))

Mike

 

[Pete_UK]

You could leave the formula as it is and apply conditional formatting
to the cell, such that if the cell content is zero then apply a white
font (which on a white background would appear to be blank).
Alternatively, change the formula to this:

=IF(G12="","",IF(VLOOKUP(g12, NameAccno,2,0)=0,"",VLOOKUP(g12,
NameAccno,2,0)))

You can use Format | Cells | Protection tab to Lock the cell and even
set it to Hidden, and this will come into effect if you use Tools |
Protection | Protect Worksheet, where you can password-protect the
sheet so that users will not be able to change the locked cells. Note,
though, that the passwords are easily broken.

Hope this helps.

Pete