if then statement use of NULL in a vlookup

[Gutierrez]

i have an excel worksheet that is returning values using a VLookUp but dont want N/a to appear instead a 0 .. how do i do this ... thanks in advance.

 

[AnotherHiggins]

=if(isna(YourFunction),0,YourFunction)

Or, if you want to capture ALL errors,

=if(iserror(YourFunction),0,YourFunction)


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

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

 

[rjoubert]

Access has a nice function called NZ. It returns a 0 if the content is null. Unfortunately, this function is not available in Excel. But you can use this user-defined function instead...

Function nz(myContent As Variant)
    If IsNull(myContent) Then nz = 0 Else nz = myContent
End Function

 

[Gutierrez]

this is what i have:

=IF(isnull(VLOOKUP(A17,'C:\Felix\[ED501531.2889.txt]ED501531.2889'!$A$1:$AJ$
437,36,FALSE)),0,VLOOKUP(A17,'C:\Felix\[ED501531.2889.txt]ED501531.2889'!$A$
1:$AJ$437,36,FALSE))

it returnes #name?

 

[AnotherHiggins]

Gutierrez,

Read my previous post and try ISNA or ISERROR. Excel does not recognize ISNULL as a worksheet function. It does know ISBLANK, but that doesn't address your original question.

FYI: You probably caused some confusion by asking a question about a worksheet function in the VBA Visual Basic for Applications (Microsoft) Forum. For future reference, this type of question belongs in forum68, the Microsoft: Office Forum.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

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

 

[Gutierrez]

Thanks John .. new here .. and like it .. get responses much quicker than other forums ... i think my other post (thread707-1266887) is more VBA related .. maybe u can help with that one ... again thanks.

 

[TonyJollans]

Just for future reference for anybody coming across this ...

Access has a nice function called NZ. It returns a 0 if the content is null. Unfortunately, this function is not available in Excel. But you can use this user-defined function instead...

Function nz(myContent As Variant)
    If IsNull(myContent) Then nz = 0 Else nz = myContent
End Function

This won't have any effect in Excel as it doesn't have nulls.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association