(Msg. 1) Posted: Mon Aug 18, 2008 8:24 am
Post subject: How can I avoid an #N/A appearing in a VLOOKUP? Archived from groups: microsoft>public>excel>crashesgpfs (more info?)
A 20 D 40
C 30 A 20
D 40 F 60
E 50 E 50
F 60 G 70
G 70 C 30
H #N/A
In this simple example, I need the H value (on right) - which is not in the
original data sheet (on left) to return a non-#N/A. In this example, i would
want to sum all the numbers but of course cannot because of the #N/A
Any help would be appreciated.
Thanks, Marc
(Msg. 2) Posted: Mon Aug 18, 2008 11:00 am
Post subject: RE: How can I avoid an #N/A appearing in a VLOOKUP? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I use an If (error( statement like:
The error captures the #N/A Result. It should look something like:
If(iserror(vlookup(x,y,z)),"-",vlookup(x,y,z))
"Marcl" wrote:
> A 20 D 40
> C 30 A 20
> D 40 F 60
> E 50 E 50
> F 60 G 70
> G 70 C 30
> H #N/A
> In this simple example, I need the H value (on right) - which is not in the
> original data sheet (on left) to return a non-#N/A. In this example, i would
> want to sum all the numbers but of course cannot because of the #N/A
> Any help would be appreciated.
> Thanks, Marc
(Msg. 3) Posted: Wed Aug 20, 2008 10:35 pm
Post subject: Re: How can I avoid an #N/A appearing in a VLOOKUP? [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Downside to that is that you also negate any other errors which may not be
what you want to do.
To specifically trap for NA you can use
=IF(ISNA(Your_Formula),0,Your_Formula)
Regards
Ken.....................
"-a" <a DeleteThis @discussions.microsoft.com> wrote in message
news:61F031F2-9684-43F0-9630-E9EEF6620917@microsoft.com...
>I use an If (error( statement like:
>
> The error captures the #N/A Result. It should look something like:
>
> If(iserror(vlookup(x,y,z)),"-",vlookup(x,y,z))
>
>
> "Marcl" wrote:
>
>> A 20 D 40
>> C 30 A 20
>> D 40 F 60
>> E 50 E 50
>> F 60 G 70
>> G 70 C 30
>> H #N/A
>> In this simple example, I need the H value (on right) - which is not in
>> the
>> original data sheet (on left) to return a non-#N/A. In this example, i
>> would
>> want to sum all the numbers but of course cannot because of the #N/A
>> Any help would be appreciated.
>> Thanks, Marc
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office