Hiding or changing #N/A value

I have the following formula, =VLOOKUP($D$2,Table!$A$1:$B$26,2,False)*G2,
that returns #N/A in numerous cells. Obviously this is not professional
looking and I would like to hide them or at least make them zero's and
conditional format to hide them. I have tried the ISNA function which works
on the #N/A but it returns FALSE when I use it on a cell that otherwise
returns a value. I must have the same formula in every cell because my data
changes monthly.

Any guidance is much appreciated.

Try:

=IF(ISERROR(VLOOKUP($D$2,Table!$A$1:$B$26,2,False)),"",VLOOKUP($D$2,Table!$A$1:$B$26,2,False))

Good luck,
Henrik

"Mac Landers" wrote:

> I have the following formula, =VLOOKUP($D$2,Table!$A$1:$B$26,2,False)*G2,
> that returns #N/A in numerous cells. Obviously this is not professional
> looking and I would like to hide them or at least make them zero's and
> conditional format to hide them. I have tried the ISNA function which works
> on the #N/A but it returns FALSE when I use it on a cell that otherwise
> returns a value. I must have the same formula in every cell because my data
> changes monthly.
>
> Any guidance is much appreciated.
>

Henrik~
Thank you; however, EXCEL doesn't like the "" part of the formula. I will
continue to manipulate the formula, but as of now Excel won't allow it, is
says there is an error in the Help Bubble and won't let me hit ENTER.

Again, any assistance is appreciated. Thank you

"Henrik" wrote:

> Try:
>
> =IF(ISERROR(VLOOKUP($D$2,Table!$A$1:$B$26,2,False)),"",VLOOKUP($D$2,Table!$A$1:$B$26,2,False))
>
> Good luck,
> Henrik
>
> "Mac Landers" wrote:
>
> > I have the following formula, =VLOOKUP($D$2,Table!$A$1:$B$26,2,False)*G2,
> > that returns #N/A in numerous cells. Obviously this is not professional
> > looking and I would like to hide them or at least make them zero's and
> > conditional format to hide them. I have tried the ISNA function which works
> > on the #N/A but it returns FALSE when I use it on a cell that otherwise
> > returns a value. I must have the same formula in every cell because my data
> > changes monthly.
> >
> > Any guidance is much appreciated.
> >

Mac

Works fine for me as copied from Henrik's post then adding the *G2 which
Henrik missed.

You sure you are copying all of Henrik's formula?

You do have a sheet named Table?

There should be no spaces in "". Watch for that.

I could provide you with a macro that would change all the formulas to IF(ISNA
or IF(ISERROR at one go if you have many of them to edit.


Gord Dibben Excel MVP


On Wed, 26 Jan 2005 09:25:03 -0800, "Mac Landers"
<MacLanders@discussions.microsoft.com> wrote:

>Henrik~
>Thank you; however, EXCEL doesn't like the "" part of the formula. I will
>continue to manipulate the formula, but as of now Excel won't allow it, is
>says there is an error in the Help Bubble and won't let me hit ENTER.
>
>Again, any assistance is appreciated. Thank you
>
>"Henrik" wrote:
>
>> Try:
>>
>> =IF(ISERROR(VLOOKUP($D$2,Table!$A$1:$B$26,2,False)),"",VLOOKUP($D$2,Table!$A$1:$B$26,2,False))
>>
>> Good luck,
>> Henrik
>>
>> "Mac Landers" wrote:
>>
>> > I have the following formula, =VLOOKUP($D$2,Table!$A$1:$B$26,2,False)*G2,
>> > that returns #N/A in numerous cells. Obviously this is not professional
>> > looking and I would like to hide them or at least make them zero's and
>> > conditional format to hide them. I have tried the ISNA function which works
>> > on the #N/A but it returns FALSE when I use it on a cell that otherwise
>> > returns a value. I must have the same formula in every cell because my data
>> > changes monthly.
>> >
>> > Any guidance is much appreciated.
>> >

Gord-
I entered it exactly the way he displayed it. I did get it to work by not
putting anything between the commas:

=IF(ISERROR(VLOOKUP($B$3,Table!$A$1:$B$26,2,FALSE)),,(VLOOKUP($B$3,Table!$A$1:$B$26,2,FALSE)))*C3.

Do you forsee any problems with my method and do you have any idea why the
"" won't work?

I apprecaite the help, as always you guys are life savers.

Mac

"Gord Dibben" wrote:

> Mac
>
> Works fine for me as copied from Henrik's post then adding the *G2 which
> Henrik missed.
>
> You sure you are copying all of Henrik's formula?
>
> You do have a sheet named Table?
>
> There should be no spaces in "". Watch for that.
>
> I could provide you with a macro that would change all the formulas to IF(ISNA
> or IF(ISERROR at one go if you have many of them to edit.
>
>
> Gord Dibben Excel MVP
>
>
> On Wed, 26 Jan 2005 09:25:03 -0800, "Mac Landers"
> <MacLanders@discussions.microsoft.com> wrote:
>
> >Henrik~
> >Thank you; however, EXCEL doesn't like the "" part of the formula. I will
> >continue to manipulate the formula, but as of now Excel won't allow it, is
> >says there is an error in the Help Bubble and won't let me hit ENTER.
> >
> >Again, any assistance is appreciated. Thank you
> >
> >"Henrik" wrote:
> >
> >> Try:
> >>
> >> =IF(ISERROR(VLOOKUP($D$2,Table!$A$1:$B$26,2,False)),"",VLOOKUP($D$2,Table!$A$1:$B$26,2,False))
> >>
> >> Good luck,
> >> Henrik
> >>
> >> "Mac Landers" wrote:
> >>
> >> > I have the following formula, =VLOOKUP($D$2,Table!$A$1:$B$26,2,False)*G2,
> >> > that returns #N/A in numerous cells. Obviously this is not professional
> >> > looking and I would like to hide them or at least make them zero's and
> >> > conditional format to hide them. I have tried the ISNA function which works
> >> > on the #N/A but it returns FALSE when I use it on a cell that otherwise
> >> > returns a value. I must have the same formula in every cell because my data
> >> > changes monthly.
> >> >
> >> > Any guidance is much appreciated.
> >> >
>
>

Not to complicate matters but this UserDefinedFunction could also be used.
It's shorter and usually faster then the normal =VLOOKUP.

' Example =LOOKUPV (E1, A1:C10, 2, FALSE, "")
Function LOOKUPV(Lookup_Value, Table_Array As Range, Col_Index_Num,
Range_value, Optional Error_Msg)
LOOKUPV = Application.VLookup(Lookup_Value, Table_Array, Col_Index_Num,
Range_value)
If IsError(LOOKUPV) And Not IsMissing(Error_Msg) Then LOOKUPV =
Error_Msg
End Function

Ola Sandstrom

Thanks for your help, but that was over my head.

"Ola" wrote:

> Not to complicate matters but this UserDefinedFunction could also be used.
> It's shorter and usually faster then the normal =VLOOKUP.
>
> ' Example =LOOKUPV (E1, A1:C10, 2, FALSE, "")
> Function LOOKUPV(Lookup_Value, Table_Array As Range, Col_Index_Num,
> Range_value, Optional Error_Msg)
> LOOKUPV = Application.VLookup(Lookup_Value, Table_Array, Col_Index_Num,
> Range_value)
> If IsError(LOOKUPV) And Not IsMissing(Error_Msg) Then LOOKUPV =
> Error_Msg
> End Function
>
> Ola Sandstrom

No idea why the "" won't work.

Your amended formula with the two ,, will work but return a 0 if value not
found.

Try this slightly amended formula......parens changed around.

=IF(ISNA(VLOOKUP($B$3,Table!$A$1:$B$26,2,FALSE)*C3),"",VLOOKUP($B$3,Table!$A$1:$B$26,2,FALSE)*C3)


Gord

On Wed, 26 Jan 2005 14:41:05 -0800, "Mac Landers"
<MacLanders@discussions.microsoft.com> wrote:

>Gord-
>I entered it exactly the way he displayed it. I did get it to work by not
>putting anything between the commas:
>
>=IF(ISERROR(VLOOKUP($B$3,Table!$A$1:$B$26,2,FALSE)),,(VLOOKUP($B$3,Table!$A$1:$B$26,2,FALSE)))*C3.
>
>Do you forsee any problems with my method and do you have any idea why the
>"" won't work?
>
>I apprecaite the help, as always you guys are life savers.
>
>Mac
>
>"Gord Dibben" wrote:
>
>> Mac
>>
>> Works fine for me as copied from Henrik's post then adding the *G2 which
>> Henrik missed.
>>
>> You sure you are copying all of Henrik's formula?
>>
>> You do have a sheet named Table?
>>
>> There should be no spaces in "". Watch for that.
>>
>> I could provide you with a macro that would change all the formulas to IF(ISNA
>> or IF(ISERROR at one go if you have many of them to edit.
>>
>>
>> Gord Dibben Excel MVP
>>
>>
>> On Wed, 26 Jan 2005 09:25:03 -0800, "Mac Landers"
>> <MacLanders@discussions.microsoft.com> wrote:
>>
>> >Henrik~
>> >Thank you; however, EXCEL doesn't like the "" part of the formula. I will
>> >continue to manipulate the formula, but as of now Excel won't allow it, is
>> >says there is an error in the Help Bubble and won't let me hit ENTER.
>> >
>> >Again, any assistance is appreciated. Thank you
>> >
>> >"Henrik" wrote:
>> >
>> >> Try:
>> >>
>> >> =IF(ISERROR(VLOOKUP($D$2,Table!$A$1:$B$26,2,False)),"",VLOOKUP($D$2,Table!$A$1:$B$26,2,False))
>> >>
>> >> Good luck,
>> >> Henrik
>> >>
>> >> "Mac Landers" wrote:
>> >>
>> >> > I have the following formula, =VLOOKUP($D$2,Table!$A$1:$B$26,2,False)*G2,
>> >> > that returns #N/A in numerous cells. Obviously this is not professional
>> >> > looking and I would like to hide them or at least make them zero's and
>> >> > conditional format to hide them. I have tried the ISNA function which works
>> >> > on the #N/A but it returns FALSE when I use it on a cell that otherwise
>> >> > returns a value. I must have the same formula in every cell because my data
>> >> > changes monthly.
>> >> >
>> >> > Any guidance is much appreciated.
>> >> >
>>
>>

Gord-
The formula worked. I had a conditional format that hides zero so my
original formual was returning 0's.

Thanks for everyones help.

"Gord Dibben" wrote:

> No idea why the "" won't work.
>
> Your amended formula with the two ,, will work but return a 0 if value not
> found.
>
> Try this slightly amended formula......parens changed around.
>
> =IF(ISNA(VLOOKUP($B$3,Table!$A$1:$B$26,2,FALSE)*C3),"",VLOOKUP($B$3,Table!$A$1:$B$26,2,FALSE)*C3)
>
>
> Gord
>
> On Wed, 26 Jan 2005 14:41:05 -0800, "Mac Landers"
> <MacLanders@discussions.microsoft.com> wrote:
>
> >Gord-
> >I entered it exactly the way he displayed it. I did get it to work by not
> >putting anything between the commas:
> >
> >=IF(ISERROR(VLOOKUP($B$3,Table!$A$1:$B$26,2,FALSE)),,(VLOOKUP($B$3,Table!$A$1:$B$26,2,FALSE)))*C3.
> >
> >Do you forsee any problems with my method and do you have any idea why the
> >"" won't work?
> >
> >I apprecaite the help, as always you guys are life savers.
> >
> >Mac
> >
> >"Gord Dibben" wrote:
> >
> >> Mac
> >>
> >> Works fine for me as copied from Henrik's post then adding the *G2 which
> >> Henrik missed.
> >>
> >> You sure you are copying all of Henrik's formula?
> >>
> >> You do have a sheet named Table?
> >>
> >> There should be no spaces in "". Watch for that.
> >>
> >> I could provide you with a macro that would change all the formulas to IF(ISNA
> >> or IF(ISERROR at one go if you have many of them to edit.
> >>
> >>
> >> Gord Dibben Excel MVP
> >>
> >>
> >> On Wed, 26 Jan 2005 09:25:03 -0800, "Mac Landers"
> >> <MacLanders@discussions.microsoft.com> wrote:
> >>
> >> >Henrik~
> >> >Thank you; however, EXCEL doesn't like the "" part of the formula. I will
> >> >continue to manipulate the formula, but as of now Excel won't allow it, is
> >> >says there is an error in the Help Bubble and won't let me hit ENTER.
> >> >
> >> >Again, any assistance is appreciated. Thank you
> >> >
> >> >"Henrik" wrote:
> >> >
> >> >> Try:
> >> >>
> >> >> =IF(ISERROR(VLOOKUP($D$2,Table!$A$1:$B$26,2,False)),"",VLOOKUP($D$2,Table!$A$1:$B$26,2,False))
> >> >>
> >> >> Good luck,
> >> >> Henrik
> >> >>
> >> >> "Mac Landers" wrote:
> >> >>
> >> >> > I have the following formula, =VLOOKUP($D$2,Table!$A$1:$B$26,2,False)*G2,
> >> >> > that returns #N/A in numerous cells. Obviously this is not professional
> >> >> > looking and I would like to hide them or at least make them zero's and
> >> >> > conditional format to hide them. I have tried the ISNA function which works
> >> >> > on the #N/A but it returns FALSE when I use it on a cell that otherwise
> >> >> > returns a value. I must have the same formula in every cell because my data
> >> >> > changes monthly.
> >> >> >
> >> >> > Any guidance is much appreciated.
> >> >> >
> >>
> >>
>
>

Mac

Thanks for the feedback.

That CF can be tricky<g>


Gord Dibben Excel MVP

On Thu, 27 Jan 2005 13:49:04 -0800, "Mac Landers"
<MacLanders@discussions.microsoft.com> wrote:

>Gord-
>The formula worked. I had a conditional format that hides zero so my
>original formual was returning 0's.
>
>Thanks for everyones help.
>
>"Gord Dibben" wrote:
>
>> No idea why the "" won't work.
>>
>> Your amended formula with the two ,, will work but return a 0 if value not
>> found.
>>
>> Try this slightly amended formula......parens changed around.
>>
>> =IF(ISNA(VLOOKUP($B$3,Table!$A$1:$B$26,2,FALSE)*C3),"",VLOOKUP($B$3,Table!$A$1:$B$26,2,FALSE)*C3)
>>
>>
>> Gord
>>
>> On Wed, 26 Jan 2005 14:41:05 -0800, "Mac Landers"
>> <MacLanders@discussions.microsoft.com> wrote:
>>
>> >Gord-
>> >I entered it exactly the way he displayed it. I did get it to work by not
>> >putting anything between the commas:
>> >
>> >=IF(ISERROR(VLOOKUP($B$3,Table!$A$1:$B$26,2,FALSE)),,(VLOOKUP($B$3,Table!$A$1:$B$26,2,FALSE)))*C3.
>> >
>> >Do you forsee any problems with my method and do you have any idea why the
>> >"" won't work?
>> >
>> >I apprecaite the help, as always you guys are life savers.
>> >
>> >Mac
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> Mac
>> >>
>> >> Works fine for me as copied from Henrik's post then adding the *G2 which
>> >> Henrik missed.
>> >>
>> >> You sure you are copying all of Henrik's formula?
>> >>
>> >> You do have a sheet named Table?
>> >>
>> >> There should be no spaces in "". Watch for that.
>> >>
>> >> I could provide you with a macro that would change all the formulas to IF(ISNA
>> >> or IF(ISERROR at one go if you have many of them to edit.
>> >>
>> >>
>> >> Gord Dibben Excel MVP
>> >>
>> >>
>> >> On Wed, 26 Jan 2005 09:25:03 -0800, "Mac Landers"
>> >> <MacLanders@discussions.microsoft.com> wrote:
>> >>
>> >> >Henrik~
>> >> >Thank you; however, EXCEL doesn't like the "" part of the formula. I will
>> >> >continue to manipulate the formula, but as of now Excel won't allow it, is
>> >> >says there is an error in the Help Bubble and won't let me hit ENTER.
>> >> >
>> >> >Again, any assistance is appreciated. Thank you
>> >> >
>> >> >"Henrik" wrote:
>> >> >
>> >> >> Try:
>> >> >>
>> >> >> =IF(ISERROR(VLOOKUP($D$2,Table!$A$1:$B$26,2,False)),"",VLOOKUP($D$2,Table!$A$1:$B$26,2,False))
>> >> >>
>> >> >> Good luck,
>> >> >> Henrik
>> >> >>
>> >> >> "Mac Landers" wrote:
>> >> >>
>> >> >> > I have the following formula, =VLOOKUP($D$2,Table!$A$1:$B$26,2,False)*G2,
>> >> >> > that returns #N/A in numerous cells. Obviously this is not professional
>> >> >> > looking and I would like to hide them or at least make them zero's and
>> >> >> > conditional format to hide them. I have tried the ISNA function which works
>> >> >> > on the #N/A but it returns FALSE when I use it on a cell that otherwise
>> >> >> > returns a value. I must have the same formula in every cell because my data
>> >> >> > changes monthly.
>> >> >> >
>> >> >> > Any guidance is much appreciated.
>> >> >> >
>> >>
>> >>
>>
>>