#NA

D

Dewi

I have some lookup data in blank fields. If there is no data, then #NA
is displayed, how can I hid the #NA without affecting the look up?

Thanks.
 
J

JulieD

Hi Dewi

embed your formula in an IF statement
here's two different approaches

=IF(ISNA(your formula),"",your formula)
OR
=IF(cell_ref="","",your formula)

Cheers
JulieD
 
D

Dewi

I have some lookup data in blank fields. If there is no data, then #NA
is displayed, how can I hid the #NA without affecting the look up?

Thanks.
Click to expand...

I being a bit slow here but...

This is my formula (I have named the range)
=LOOKUP(A18,serial,item)

How should your suggestions look in my formula?

thanks
 
B

bcmiller

Hi There,

It is better to use a generic error trapping mechanism (ie: one tha
will trap all errors and just #N/A). To do this you can use ISERROR i
place of ISNA eg:

=IF(ISERROR(LOOKUP(A18,serial,item)),"",LOOKUP(A18,serial,item))

Cheers,

B
 
G

Gord Dibben

I don't agree that the generic ISERROR is better. It would hide ANY error,
not just the #N/A.

Suppose this formula........

=VLOOKUP(D1,A1:B10,2,FALSE)*E1

If E1 is not a numeric, you will get #VALUE returned.

The VLOOKUP value may be correct but an alternate error exists that would be
masked by the ISERROR.

This may supply users with a false sense of security about their results.

Gord Dibben Excel MVP
 
A

Aladin Akyurek

You can get #N/A with a LOOKUP formula only when the lookup value in A1
is smaller than the first value in the first column of 'serial'. If it'
fully justified to use LOOKUP(), then one possibility is to test fo
whether the foregoing situation arises:

=IF(A18<INDEX(serial,1,1),"",LOOKUP(A18,serial,item))
 
B

bcmiller

Hi Gord,

It is true that ISERROR will trap all errors. I suggested using thi
as it makes for a professional looking output. If you need to trap a
individual error so that you can replace it with an alternate value
then trap the individual error, but also use ISERROR to trap othe
errors and also replace them. Thus by replacing the error message wit
an alternate warning/meaningful error message the user is not lulle
into a false sense of security. Good practice really.

Cheers,

B
 
Top