Vlookup returning #N/A

[Guest]

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.

 

[Harry Stottle]

-----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

 

[Gord Dibben]

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