|
ISERR
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 |
ISERR
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 "Jools" wrote: 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 |
ISERR
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 On Feb 20, 10:01*am, Jools wrote: 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 |
ISERR
Hi,
Thanks to both Mike & Pete. Both work for what I need. thanks Jools "Jools" wrote: 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 |
ISERR
Pete_UK;239009 Wrote: 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 On Feb 20, 10:01*am, Jools wrote: 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 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),"") -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=66730 |
ISERR
Yes, but you'll have to make the table references absolute so that you
can copy it down. Pete On Feb 20, 10:36*am, Pecoflyer wrote: 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),"") -- Pecoflyer Cheers - |
ISERR
Glad to hear it - thanks for feeding back.
Pete On Feb 20, 10:33*am, Jools wrote: Hi, Thanks to both Mike & Pete. *Both work for what I need. thanks Jools |
ISERR
Pete_UK;239068 Wrote: Yes, but you'll have to make the table references absolute so that you can copy it down. Pete On Feb 20, 10:36*am, Pecoflyer wrote: 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),"") -- Pecoflyer Cheers - I wondered how long it would take... -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=66730 |
| All times are GMT +1. The time now is 06:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com