Vlookup returning #N/A

Hi all I could use some help with a Vlookup.

I have a range name of 'rng' on sheet A.
Column A
1
2
3
4
5
6

In a different file I have a range name 'table'
Column A Column B
1 20
3 5
4 7
6 13

My problem is with a vlookup since certain numbers don't exist in 'table' it
returns a #N/A so I end up with.

Column A Column B
1 20
2 #N/A
3 5
4 7
5 #N/A
6 13

Is there a way to return a '0' instead of #N/A on a vlookup so I can still
sum Column B? Thanks for any help in advance.

>-----Original Message-----
>Hi all I could use some help with a Vlookup.
>
>I have a range name of 'rng' on sheet A.
>Column A
>1
>2
>3
>4
>5
>6
>
>In a different file I have a range name 'table'
>Column A Column B
>1 20
>3 5
>4 7
>6 13
>
>My problem is with a vlookup since certain numbers don't
exist in 'table' it
>returns a #N/A so I end up with.
>
>Column A Column B
>1 20
>2 #N/A
>3 5
>4 7
>5 #N/A
>6 13
>
>Is there a way to return a '0' instead of #N/A on a
vlookup so I can still
>sum Column B? Thanks for any help in advance.
>.
>I suggest a possible solution is to insert the formula "
=IF(VLOOKUP(E4,Table,1)=E4,VLOOKUP(E4,Table,2),0)".

The condition looks for a match of the data in column A
and if the value exists returns the value, else returns a
zero.

Should you enter a value less than 1 then an error will
still be produced. The best policy is to have the first
line of the table with a zero value:

Column A Column B
0 0
1 20
3 5 etc

Hope this solves your problem.

Regards

Harry

ww

=ISNA(Vlookup formula,0,Vlookup formula)

e.g. =IF(ISNA(VLOOKUP(D1,A1:B12,2,FALSE)),0,VLOOKUP(D1,A1:B12,2,FALSE))

Alternative to show blank, not zero.

=IF(ISNA(VLOOKUP(D1,A1:B12,2,FALSE)),"",VLOOKUP(D1,A1:B12,2,FALSE))


Gord Dibben Excel MVP


On Tue, 22 Mar 2005 15:05:06 -0800, "ww" <[email protected]> wrote:

>Hi all I could use some help with a Vlookup.
>
>I have a range name of 'rng' on sheet A.
>Column A
>1
>2
>3
>4
>5
>6
>
>In a different file I have a range name 'table'
>Column A Column B
>1 20
>3 5
>4 7
>6 13
>
>My problem is with a vlookup since certain numbers don't exist in 'table' it
>returns a #N/A so I end up with.
>
>Column A Column B
>1 20
>2 #N/A
>3 5
>4 7
>5 #N/A
>6 13
>
>Is there a way to return a '0' instead of #N/A on a vlookup so I can still
>sum Column B? Thanks for any help in advance.