vlookup avoiding #N/A
 

Hi!

If anybody know a way around this I'll be forever thankfull.

I have a standard list which uses vlookup to another list which varies.
If there is not a match I get #N/A and that messes up my subtotals in
the first list. I have tried variations with if, match and find but I
can not make it work.

N2=VLOOKUP(B2;Internal!B:C;2;FALSE)
N2=FD312 Insurance

Thanks in advance


--
Lillian Eik
------------------------------------------------------------------------
Lillian Eik's Profile: http://www.excelforum.com/member.php...o&userid=24919
View this thread: http://www.excelforum.com/showthread...hreadid=384575

=if(iserror(match(b2;c:c;0)),"",VLOOKUP(B2;Interna l!B:C;2;FALSE))

"Lillian Eik"
wrote in message
...

Hi!

If anybody know a way around this I'll be forever thankfull.

I have a standard list which uses vlookup to another list which varies.
If there is not a match I get #N/A and that messes up my subtotals in
the first list. I have tried variations with if, match and find but I
can not make it work.

N2=VLOOKUP(B2;Internal!B:C;2;FALSE)
N2=FD312 Insurance

Thanks in advance


--
Lillian Eik
------------------------------------------------------------------------
Lillian Eik's Profile:
http://www.excelforum.com/member.php...o&userid=24919
View this thread: http://www.excelforum.com/showthread...hreadid=384575

try this:
IF(ISNA(VLOOKUP($B15,$J$14:$N$60,2,FALSE)),0,VLOOK UP($B15,$J$14:$N$60,2,FALSE))

"Lillian Eik" wrote:


Hi!

If anybody know a way around this I'll be forever thankfull.

I have a standard list which uses vlookup to another list which varies.
If there is not a match I get #N/A and that messes up my subtotals in
the first list. I have tried variations with if, match and find but I
can not make it work.

N2=VLOOKUP(B2;Internal!B:C;2;FALSE)
N2=FD312 Insurance

Thanks in advance


--
Lillian Eik
------------------------------------------------------------------------
Lillian Eik's Profile: http://www.excelforum.com/member.php...o&userid=24919
View this thread: http://www.excelforum.com/showthread...hreadid=384575

=IF(ISNUMBER(MATCH(B2;Internal!B:B;0));VLOOKUP(B2; Internal!B:C;2;FALSE);0)

would be one way where 0 would be returned if there is no match, if you want
what looks like an empty cell

=IF(ISNUMBER(MATCH(B2;Internal!B:B;0));VLOOKUP(B2; Internal!B:C;2;FALSE);"")


--
Regards,

Peo Sjoblom

(No private emails please)


"Lillian Eik"
wrote in message
...

Hi!

If anybody know a way around this I'll be forever thankfull.

I have a standard list which uses vlookup to another list which varies.
If there is not a match I get #N/A and that messes up my subtotals in
the first list. I have tried variations with if, match and find but I
can not make it work.

N2=VLOOKUP(B2;Internal!B:C;2;FALSE)
N2=FD312 Insurance

Thanks in advance


--
Lillian Eik
------------------------------------------------------------------------
Lillian Eik's Profile:
http://www.excelforum.com/member.php...o&userid=24919
View this thread: http://www.excelforum.com/showthread...hreadid=384575

If we use ISERROR to trap error messages, we can prevent them from
showing up, as such:

=IF(ISERROR(VLOOKUP(B2;Internal!B:C;2;FALSE)),"",V LOOKUP(B2;Internal!B:C;2;FALSE)

The first part of the formula looks to see if the result is an error
and if so, returns the empy string "". If no error is found, the
result of the formula is returned.

HTH

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=384575

Just a head's up on ISERROR

It will mask all errors, not just the #N/A and may hide something you don't
want hidden.

Preferable to use the ISNA function.

=IF(ISNA(VLOOKUP(B2;Internal!B:C;2;FALSE)),"",VLOO KUP(B2;Internal!B:C;2;FALSE)


Gord Dibben Excel MVP

On Tue, 5 Jul 2005 11:41:21 -0500, swatsp0p
wrote:


If we use ISERROR to trap error messages, we can prevent them from
showing up, as such:

=IF(ISERROR(VLOOKUP(B2;Internal!B:C;2;FALSE)),"", VLOOKUP(B2;Internal!B:C;2;FALSE)

The first part of the formula looks to see if the result is an error
and if so, returns the empy string "". If no error is found, the
result of the formula is returned.

HTH

Bruce

Thanks for the tip, Gord. I'll keep that in mind as I evaluate the need
for error trapping methods.

Cheers!

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=384575