vlookup

[Phill W]

if a cell has a VLOOKUP formular in it, can i lock this cell so I can type in it with affecting the formular ?

 

[GlennUK]

Don't you mean that you want to lock the cell so that you can't type in it?

 

[Phill W]

naaa if cell is blank after vlookup, i may want to type a comment in cell, but do not want to loose formular in cell

 

[GlennUK]

So why not use the inbuilt Insert Comment functionality of Excel?

 

[Phill W]

i need to print this document each day

 

[Stu Dapples]

Not really, if you type over a cell, its original contents are lost....

You can incorporate a sort of error handling statement into your Vlookup though:

=IF(Vlookup(value,range,offset,false)="","your comment",vlookup(value,range,offset,false))

Both the Vlookup statements are the same, if it returns a "" (blank) value, your text will be displayed instead....

I have used it with "", isna, iserror, its quite effective at removing grubby looking errors off your sheets!

 

[Stu Dapples]

Or you could use a cell reference instead of the "your comment", that way you could type in to the target cell whatever you want displayed that day...

 

[Phill W]

can vlookup, look up 2 cells but search same table ?

 

[GlennUK]

can vlookup, look up 2 cells but search same table ?

Using what logic?

Is it going to be vlookup when cell1 = contents of column B and where cell2 = contents of column C

Or do you mean: vlookup using cell1, but if that fails then do vlookup using cell2

?

If you describe your questions more clearly, it would be a help.