vlook up and if formula

[Guest]

hi

im playing with a spreadsheet at the moment trying to use vlookup which i
have working

however, instead of when the vlookup value providing a value of n/a when
false, is there a way that i can use an if statement to return a value of "0"
(zero)

thanks

stuart

 

[Leo Heuser]

Hi Stuart

One way:

=IF(ISERROR(Your formula),0,Your formula)

 

[arno]

Hi Stuart,

however, instead of when the vlookup value providing a value of n/a
when false, is there a way that i can use an if statement to return a
value of "0" (zero)

you need your formula twice, eg.

=if(iserror(vlookup(a1,data,2,false)),0,vlookup(a1,data,2,false))

where 0 in the middle of the formula is what you want. sometimes I use
"not found", "---" or just "", depending on what is usefull in the
situation, 0 is fine, too.

arno

 

[Guest]

=if(isna(vlookup formula),0,vlookup formula)

 

[Guest]

thanks for everyone's help

stuart

 

[Leo Heuser]

You're welcome, Stuart

LeoH