vlookup question

[slindale]

I am using vlookup (exact matcht) to retreive data from another spreadheet in the same workbook. Sometimes there will not be a match. How can I make excel return a value of zero if there is no match? I have been working on this for a long time. Any ideas would be greatly appreciated.

Thanks,

 

[JamieDuncan]

How about this

=IF(VLOOKUP(A1,B1:C10,1)=A1,VLOOKUP(A1,B1:C10,2),0)

 

[Asala42]

I'd wrap an if-statement around it.

Instead of:
=VLOOKUP(A2,lookup_rng,2,0)

Use something like:
=IF(COUNTIF(lookup_rng,A2)=0,0,VLOOKUP(A2,lookup_rng,2,0))

HTH

 

[slindale]

Thanks alot for the ideas. The countif worked great for me. Thanks everyone.

 

[JamieDuncan]

Using the COUNTIF statement is a better idea, the formula I made isnt exactly fullproof, VLOOKUP being the fickle beast it is.