Suppress #N/A

[waynerenaud]

I am using a vlookup in excel2002. It works fine, but when it doesnt match the criteria you get #N/A, is there any way to suppress this or make it 0 (Zero).
Thanks

 

[Zathras]

You can use a combination of IF() ISNA() and VLOOKUP() like this:
[blue]

  =IF(ISNA(VLOOKUP(A1,LookupTable,2,0)),0,VLOOKUP(A1,LookupTable,2,0))

[/color]

 

[waynerenaud]

Many Thanks Zathras,
I was struggling trying to use ISERR, but got to the correct result using ISERROR:
=IF(ISERROR(VLOOKUP(A2,run2!A:G,7,FALSE)),0,VLOOKUP(A2,run2!A:G,7,FALSE)).

Many Thanks for the help and fast reply. This forum (and particularly many of your posts) has and is teaching me heaps.

 

[xlbo]

waynerenaud
Coupla things - you should be fine using ISERROR but it is better to use ISNA as it is checking for the specific instance of the data not being found. Other errors can occur (albeit rarely) when using vlookups - such as #REF! errors when the offset is greater than the width of the table
Also, when selecting your range to do the lookup in, it is better NOT tot select entire columns as if you have more than a coupla hundred lookups, it'll start impacting on your re-calc speed

Rgds, Geoff
[blue]Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?[/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[waynerenaud]

Cool, thanks xlbo. I will take your advice and make the necessary changes. I just keep on learning.........