vlookup returning #N/A 1

[bac2u]

I'm using nested vlookup functions like this:

=Sum(vlookup(a2,range1,2,false)+vlookup(a2,range2,2,false)-vlookup(a2,range3,2,false))

and in some cells I'm getting only #N/A because one of the ranges (it could be any of the three) doesn't have an exact match to A2 in it.

How can I make the formula return zero for the range it didn't find the match in instead of #n/a, so that I get an answer to Sum.

Example: A2 in range 1 = 50, range 2 = 100, range 3 = #N/A
I want the sum of 150 in that cell instead of #N/A

any help in getting around this is appreciated!

 

[xlbo]

This type of cponstruct should be all you need

=If(ISNA(Vlookup(a2,range1,2,false)),0,Vlookup(a2,range1,2,false))

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[bac2u]

Thanks so much, that should do the trick.