Return zeros instead of #N/A on VLOOKUP 1

[ranebow]

Is there a way to return either blanks or zeros on a VLOOKUP when you don't get a hit?

I'm doing a lookup from a "chart of accounts total sheet" to each month's expenses. If I don't get a hit I don't want to see the #N/A on my sheet. The #N/A also gives me problems when trying to total that column.

Any suggestions?

 

[earthandfire]

Try placing the VLOOKUP in an IF function.


Hope this helps.

 

[AnotherHiggins]

Use the IsError or IsNa function. It will look like this:

[COLOR=blue white]=if(isna(yourVLookup),0,yourVLookup)[/color]

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[ranebow]

Ok, I'm still having problems.

I've entered anotherhiggins formula and keep getting an error.

 

[ranebow]

Here's my formula:


=if(isna(VLOOKUP(A3,AprilPivot!$D$5:$E$24,2,false),0,VLOOKUP(A3,AprilPivot!$D$5:$E$24,2,false))

 

[Quandan]

Ranebow,

Your formula should read:

=if(isna(VLOOKUP(A3,AprilPivot!$D$5:$E$24,2,false)),0,VLOOKUP(A3,AprilPivot!$D$5:$E$24,2,false))

notice the second ) halfway the formula

Kind regards,

Marcel Kreijne
Quandan - Steunpunt voor spreadsheetgebruikers
(Quandan - Dutch supportsite for spreadsheetusers)

http://www.quandan.nl

 

[AnotherHiggins]

You are missing a close parenthesis. Try this:

[COLOR=blue white]=if(isna(VLOOKUP(A3,AprilPivot!$D$5:$E$24,2,false)[highlight])[/highlight],0,VLOOKUP(A3,AprilPivot!$D$5:$E$24,2,false))[/color]

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[ranebow]

THANK YOU!!!!

That is exactly what I want it to do!

 

[AnotherHiggins]

Glad we could help!


[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.