excel 97 #N/A 1

[dreadnaught]

when doing a vlookup and the value is returned as an #N/A how do you convert that to a value of "0"?

 

[Databaseguy]

If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

 

[Goska]

Try this
=if(isna(vlookup( , , )),0,Something if it is available)

 

[dreadnaught]

goska

here is the formula I put in =IF(ISNA(VLOOKUP($A$4,dat,3,FALSE)),0,VLOOKUP($A$4,dat,3,FALSE)). It did change the NA to a zero value but if it was not to be an NA it returned zero also. Any idea what went wrong?

Dreadnaught

 

[Goska]

Check to make sure that your VLOOKUP function is correct.

I tested this, and it works to look for the value in A4 and match it to a value in A8:A13 and pull the corresponding number out of C8:C13

=IF(ISNA(VLOOKUP($A$4,A8:C13,3,FALSE)),0,VLOOKUP($A$4,A8:C13,3,FALSE))

My best guess is some kind of problem with the dat range. I always have problems with the named ranges, so I don't use them and can't give you any advice on that. Try it with a direct cell reference and make sure that it is working right like that, then try the named range thing again. My best guess is that dat got messed up and is not defined over the correct range. Like I said, I don't use them, so try the original
=VLOOKUP($A$4,dat,3,FALSE)
and see if you get the right answers, with the N/A

Then try it my way, see if it works right. If you are still having problems, post again. Good Luck.

 

[Databaseguy]

Howzbout a slight change:

IF(ISNA(VLOOKUP($A$4,dat,3,FALSE))= True,0,VLOOKUP($A$4,dat,3,FALSE)).

 

[Goska]

The true is implied, in Excel 97 anyway.

=IF(ISNA(VLOOKUP($A$4,dat,3,FALSE)),0,VLOOKUP($A$4,dat,3,FALSE))

Works just fine once I set some ranges for dat.

 

[dreadnaught]

Gentlemen,

I would like to thank you both for responding so quickly and accurately. I have not been able to get my IT folks to solve this for me but they did give me this web site. You both are very good at what you do.

Again Thanks!!!

Dreadnaught