IF ISNA error

**djsouljah** · 10-29-2011, 10:03 PM

Hi There

I have a formula that's supposed to get rid of the N/A error and replace it with a 0

But it's not working for some reason :

=IF(ISNA(VLOOKUP(F15,'Stat remedy et connect'!A$1:E$9946,FALSE)),0,VLOOKUP(F15,'Stat remedy et connect'!A$1:E$942,4,FALSE))

Anyone can tell me why I'm still getting N/A ??

**alansidman** · 10-29-2011, 10:28 PM

In your first Vlookup, you are missing the column that should return results from.

Alan

**TMShucks** · 10-29-2011, 10:30 PM

Might be because the ranges shown are different. 9946 vs 942?

If you're using 2007, you could use IFERROR.

Regards

**djsouljah** · 10-29-2011, 10:51 PM

in this case, it is supposed to return an N/A error as when it looking up the info, it's not there...that's ok...but if it returns N/A i want it to echo 0...which is what the IF ISNA is supposed to do in my understanding.

It's because somewhere else on my file it would be calculating the average and when there's N/A, even my calculation for the average =SUM($AD$3:$AD$122)/SUM($AE3:$AE112) is displaying N/A.

**TMShucks** · 10-29-2011, 10:56 PM

There are still two errors in your formula. The first VLOOKUP is missing a column entry and the ranges are different.

If however, the value that it finds is #N/A, I guess it's going to return that.

Regards

**nadarajan** · 10-29-2011, 10:57 PM

If there is any text found in the calculating area (i.e ad3:ad122 and ae3:ae112) you will be getting N/a
and in the first formula as alan said you have missed the column value.