ISERR

[Jools]

Hi

I am trying to input the follwing formula however when using ISERR it will
only return the word FALSE. I am sure a couple of years ago I saw someone
use this function but they managed to return 0.00. Any ideas where I am
going wrong. I have tried substituting the FALSE for 0.00 but it does not
like it

=ISERR(VLOOKUP(B10,'Copy balance sheet'!A1:G400,7,FALSE))



Thanks
Julie

 

[Mike H]

Hi,

Are you trying to do this

=IF(ISNA(VLOOKUP(B10,'Copy Balance
Sheet'!A1:G400,7,FALSE)),"",VLOOKUP(B10,'Copy Balance Sheet'!A1:G400,7,FALSE))

Mike

 

[Pete_UK]

I think what you are trying to do is this:

=IF(ISERR(VLOOKUP(B10,'Copy balance sheet'!A1:G400,7,FALSE)),0,VLOOKUP
(B10,'Copy balance sheet'!A1:G400,7,FALSE))

i.e. if there is an error in the lookup then return 0, otherwise
return the result of the lookup. Personally, I prefer to use ISNA,
like so:

=IF(ISNA(VLOOKUP(B10,'Copy balance sheet'!A1:G400,7,FALSE)),0,VLOOKUP
(B10,'Copy balance sheet'!A1:G400,7,FALSE))

Hope this helps.

Pete

 

[Jools]

Hi,

Thanks to both Mike & Pete. Both work for what I need.

thanks
Jools

 

[Pecoflyer]

I think what you are trying to do is this:

=IF(ISERR(VLOOKUP(B10,'Copy balance sheet'!A1:G400,7,FALSE)),0,VLOOKUP
(B10,'Copy balance sheet'!A1:G400,7,FALSE))

i.e. if there is an error in the lookup then return 0, otherwise
return the result of the lookup. Personally, I prefer to use ISNA,
like so:

=IF(ISNA(VLOOKUP(B10,'Copy balance sheet'!A1:G400,7,FALSE)),0,VLOOKUP
(B10,'Copy balance sheet'!A1:G400,7,FALSE))

Hope this helps.

Pete

Maybe this formula will be faster if you have many VLOOKUP 's :
=IF(countif('Copy balance sheet'!A1:a400,B10),VLOOKUP
(B10,'Copy balance sheet'!A1:G400,7,FALSE),""

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[Pete_UK]

Yes, but you'll have to make the table references absolute so that you
can copy it down.

Pete

 

[Pete_UK]

Glad to hear it - thanks for feeding back.

Pete

 

[Pecoflyer]

Yes, but you'll have to make the table references absolute so that you
can copy it down.

Pete

I wondered how long it would take..

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste