Vlookup & #n/a

[Fishbone]

I am running a VLOOKUP formula in which I am expecting most cells t
return "#N/A". Does anyone know how I can either make the #N/A'
return blank or with different text i.e "Not Active"

The formula I am running is:-

=VLOOKUP(A4, Rae2001!D2:I2200, 6, 0)

Thanks

Laurenc

 

[JulieD]

Hi

one way
=IF(ISNA(VLOOKUP(A4, Rae2001!D2:I2200, 6, 0)),"Not Active",VLOOKUP(A4,
Rae2001!D2:I2200, 6, 0))

anyother way
=if(A4="","Not Active",VLOOKUP(A4, Rae2001!D2:I2200, 6, 0))

"Not Active" can be replace with anything you like
""
will give you blank (well more correctly, it will return a zero length
string)

Cheers
JulieD