#N/A with Vlookup

Posted by Simon McArdle on May 15, 2001 6:10 AM

Hi,

I currently have the following code.....
=IF(T8&LT;&GT;"";VLOOKUP(L8;doggy.csv!$A$1:$B$2000;2;0);"")

If nothing is there in my reference file.....how can I stop the VLOOKUP providing a #N/A into the cell.

Also...If a value already exists in the cell....How can I stop it from being overwriten with #N/A or whatever Ive substituted for #N/A ie so it first looks to see if anything is in the cell and if there is it leaves it alone. If there is not anything...it checks the vlookup and puts that data in or if there is nothing it puts nothing as opposed to #N/A

Is this a tricky one or can it be easily accomplished.

Thanks
Simon

Posted by Aladin Akyurek on May 15, 2001 6:58 AM

=IF(ISBLANK(T8),"",IF(ISNUMBER(MATCH(L8,doggy.csv!$A$1:$A$2000,0)),VLOOKUP(L8,doggy.csv!$A$1:$B$2000,2;0),"")

Aladin

Posted by lenze on May 15, 2001 7:05 AM

You could use the ISBLANK and ISERROR functions.

=IF(ISBLANK(A1),IF(ISERROR(YourVLOOKUP),"",Your Vlookup),A1)

Posted by Simon McArdle on May 15, 2001 7:30 AM

Hi,

I am now using the example.....

=IF(ISBLANK(T8);"";IF(ISNUMBER(MATCH(L8;doggy.csv!$A$1:$A$2000;0));VLOOKUP(L8;doggy.csv!$A$1:$B$2000;2;0);""))

This seems to solve my #N/A problem but it does not solve the problem if there is something already in the cell I want it to stay and not be overwritten. This current formular will overwrite anything that is already in the cell. Is it possible to first test if there is anything in the cell.....and only if the cell is empty should the above forular be brought into play.

Thanks for your help on this.

Simon

Posted by Sean on May 15, 2001 8:34 AM

Hi Simon,

=IF(T8&LT;&GT;"";if(isna(VLOOKUP(L8;doggy.csv!$A$1:$B$2000;2;0));"";VLOOKUP(L8;doggy.csv!$A$1:$B$2000;2;0);""))

The above will help you get around the N/A problem.

In the next part, I do not fully understand why it is going to override an existing cell, Are you copying a formula down the whole column?

If this is the case you will need to write a macro to test each cell before pasting the value(formula) in

waiting for some more input...

Posted by Aladin Akyurek on May 15, 2001 9:17 AM

Simon,

It is not possible for a formula to check its own cell in order to see whether there is already a value there and act on the result of that test.

You can of course use some intrusive VB code to do what you want.

Aladin

Posted by Simon McArdle on May 15, 2001 11:16 PM

Aladin,

Thanks for your help so far.

How would I add this code to a Macro.
What would the code look like.
Do I need to have a specialist VB programer or is this something I could do myself. (Im not a programer)

Any assistance would be appreciated.

Thanks
Simon

Posted by Simon McArdle on May 16, 2001 12:11 AM

Hi Sean,

Yes I am pasting down the whole column. I think I need some vb script to deal with this problem of overwriting data if it already appears in the cell. Do you have any Ideas.

Thanks
Simon