vlookup avoiding #N/A

[Lillian Eik]

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 i
the first list. I have tried variations with if, match and find but
can not make it work.

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

Thanks in advanc

 

[N Harkawat]

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

"Lillian Eik" <[email protected]>
wrote in message
news:[email protected]...

 

[Guest]

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

 

[Peo Sjoblom]

=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" <[email protected]>
wrote in message
news:[email protected]...

 

[swatsp0p]

If we use ISERROR to trap error messages, we can prevent them fro
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 erro
and if so, returns the empy string "". If no error is found, th
result of the formula is returned.

HTH

Bruc

 

[Gord Dibben]

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)),"",VLOOKUP(B2;Internal!B:C;2;FALSE)


Gord Dibben Excel MVP

 

[swatsp0p]

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

Cheers!

Bruce