Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return blank cell if value you vlookup is not on pivot | Excel Worksheet Functions | |||
Return cell adress for next non-blank cell in a range | Excel Worksheet Functions | |||
Vlookup return 0 when cell is blank | Excel Worksheet Functions | |||
find the first blank cell in a range and return me it's position | Links and Linking in Excel | |||
if the value of a cell in a range is not blank, then return the v. | Excel Worksheet Functions |