I want my lookup to say 0 if it can't find anything - help!

[Helen]

I am trying to do a simple lookup, but if it can't find what I'm looking up it results in #N/A.

I know it's possible, but just can't think how!

Is it possible to put an IF statement with it?

I know there are functions like ISERROR/ISNA etc, but I really just don't understand when these should be used, and have even been on the helps pages, but I still don't understand them !

Thanks for your help

Helen

[Aladin Akyurek]

On 2002-04-10 06:30, Helen wrote:
I am trying to do a simple lookup, but if it can't find what I'm looking up it results in #N/A.

I know it's possible, but just can't think how!

Is it possible to put an IF statement with it?

I know there are functions like ISERROR/ISNA etc, but I really just don't understand when these should be used, and have even been on the helps pages, but I still don't understand them !

Thanks for your help

Helen

Look at my post at:

Look at this:
http://www.mrexcel.com/board/viewtop...c=4277&forum=2

Aladin

[bozack]

Good question,

I assumed that:
=IF((VLOOKUP(D4,$A$1:$B$10,2,FALSE))="#N/A",0,(VLOOKUP(D4,$A$1:$B$10,2,FALSE)))

but it doesn't?

Boz

[Mark W.]

On 2002-04-10 09:39, bozack wrote:
Good question,

I assumed that:
=IF((VLOOKUP(D4,$A$1:$B$10,2,FALSE))="#N/A",0,(VLOOKUP(D4,$A$1:$B$10,2,FALSE)))

but it doesn't?

Boz

Boz, watch your data types... "#N/A" isn't the same as #N/A. Although I abhor this solution, the correct formula would be...

IF(ISNA(VLOOKUP(D4,$A$1:$B$10,2,FALSE)),0,VLOOKUP(D4,$A$1:$B$10,2,FALSE))

[ This Message was edited by: Mark W. on 2002-04-10 09:47 ]