Vlookup to return blank cell if nothing in range
 

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?

Vlookup to return blank cell if nothing in range
 

perhaps

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

Mike

"Haz" wrote:

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?

Vlookup to return blank cell if nothing in range
 

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

On Apr 15, 8:12*pm, Haz wrote:
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?