Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2006
    Posts
    65

    Unanswered: VLOOKUP Formula to return 0 instead of #N/A

    Hi,
    the following formula =VLOOKUP(B16,'Number of Accruals'!A2:F1014,6,FALSE)
    returns '#N/A' when the lookup value does not exist, i would like to be able to return a '0' in this case.

    can anyone please advise on how to do this?

    thanks in advance!

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi waylander,

    If you are using Excel 2007 you can use this:
    Code:
    =IFERROR(VLOOKUP(B16,'Number of Accruals'!A2:F1014,6,FALSE),)
    If you are using an earlier version than Excel 2007 you can use this:
    Code:
    =IF(ISNA(MATCH(B16,'Number of Accruals'!A2:A1014,0)),,VLOOKUP(B16,'Number of Accruals'!A2:F1014,6,FALSE))
    Hope that helps..

  3. #3
    Join Date
    Apr 2006
    Posts
    65
    Hi Colin,
    the second code returns a '0' even if there is a value in the range?

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I think this

    Code:
    =IF(ISERROR(VLOOKUP(B16,'Number of Accruals'!A2:F1014,6,FALSE)),,VLOOKUP(B16,'Number of Accruals'!A2:F1014,6,FALSE))
    or this
    Code:
    =IF(ISNA(VLOOKUP(B16,'Number of Accruals'!A3:F1015,6,FALSE)),,VLOOKUP(B17,'Number of Accruals'!A3:F1015,6,FALSE))
    Should do it ??

    MTB

  5. #5
    Join Date
    Apr 2006
    Posts
    65
    Colin,
    i do beg your pardon your second formula works well, i realised that i hadn't referenced the cells correctly many thanks,
    Mike your formula works well too,
    many thanks guys!!!

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Quote Originally Posted by waylander
    Colin,
    i do beg your pardon your second formula works well, i realised that i hadn't referenced the cells correctly many thanks,
    No worries, glad you got it working.

  7. #7
    Join Date
    Oct 2009
    Posts
    3

    help

    I am very new to excel, (2 days) and stumbled across this thread.
    I am stumbling my way through Excel trying to make a calculation sheet using referenced matricies in attached workbooks to calculate a score based on time, and a few other factors.

    My main problem is very close to what you have already solved, and I can't seem to get the syntax right.

    I need to return a "0" if none of the conditions exist.

    here is my line of code (i'm sure there is a better way to do it, but it is working, be gentle...lol)

    =IF((AND(G45="TRPS",BC1="STA")),VLOOKUP(AW45,troop sdefense!A1:V51,Sheet1!M45/100),IF((AND(G45="TRPS",BC1="MOV")),VLOOKUP(AW45,t roopsoffense!A1:V51,Sheet1!M45/100),IF((AND(G45="TRK",BC1="STA")),VLOOKUP(AW45,tr uckdefense!A1:V51,Sheet1!M45/100),IF((AND(G45="TRK",BC1="MOV")),VLOOKUP(AW45,tr uckoffense!A1:V51,Sheet1!M45/100),IF((AND(G45="PC",BC1="STA")),VLOOKUP(AW45,pcd efense!A1:V51,Sheet1!M45/100),IF((AND(G45="PC",BC1="MOV")),VLOOKUP(AW45,pco ffense!A1:V51,Sheet1!M45/100)))))))

    I just need it to return a value of "0" if none of these conditions exist

  8. #8
    Join Date
    May 2009
    Location
    India
    Posts
    66
    Hi pete.ford,

    Without going deeply into the statement, all you should probably do is to remember your spec which says I need to return a "0" if none of the conditions exist. ie if the final result is '#N/A' . If this is so, then it might be sufficient to enclose the entire script in one if(iserror(...)

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by pete.ford
    I am very new to excel, (2 days) and stumbled across this thread.
    I am stumbling my way through Excel trying to make a calculation sheet using referenced matricies in attached workbooks to calculate a score based on time, and a few other factors.

    My main problem is very close to what you have already solved, and I can't seem to get the syntax right.

    I need to return a "0" if none of the conditions exist.

    here is my line of code (i'm sure there is a better way to do it, but it is working, be gentle...lol)

    =IF((AND(G45="TRPS",BC1="STA")),VLOOKUP(AW45,troop sdefense!A1:V51,Sheet1!M45/100),IF((AND(G45="TRPS",BC1="MOV")),VLOOKUP(AW45,t roopsoffense!A1:V51,Sheet1!M45/100),IF((AND(G45="TRK",BC1="STA")),VLOOKUP(AW45,tr uckdefense!A1:V51,Sheet1!M45/100),IF((AND(G45="TRK",BC1="MOV")),VLOOKUP(AW45,tr uckoffense!A1:V51,Sheet1!M45/100),IF((AND(G45="PC",BC1="STA")),VLOOKUP(AW45,pcd efense!A1:V51,Sheet1!M45/100),IF((AND(G45="PC",BC1="MOV")),VLOOKUP(AW45,pco ffense!A1:V51,Sheet1!M45/100)))))))

    I just need it to return a value of "0" if none of these conditions exist
    Hi

    Without looking too hard, I think, perhapse, this may do it

    =IF((AND(G45="TRPS",BC1="STA")),VLOOKUP(AW45,troop sdefense!A1:V51,Sheet1!M45/100),IF((AND(G45="TRPS",BC1="MOV")),VLOOKUP(AW45,t roopsoffense!A1:V51,Sheet1!M45/100),IF((AND(G45="TRK",BC1="STA")),VLOOKUP(AW45,tr uckdefense!A1:V51,Sheet1!M45/100),IF((AND(G45="TRK",BC1="MOV")),VLOOKUP(AW45,tr uckoffense!A1:V51,Sheet1!M45/100),IF((AND(G45="PC",BC1="STA")),VLOOKUP(AW45,pcd efense!A1:V51,Sheet1!M45/100),IF((AND(G45="PC",BC1="MOV")),VLOOKUP(AW45,pco ffense!A1:V51,Sheet1!M45/100),0))))))

    That is, the 'False' condition in the last IF statement is zero (there is no false condition in the last IF in your statement). This last False condition is the one returened if ALL the condition in ALL the IF statements are false !!?

    HTH

    MTB

  10. #10
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    I would add:

    [1] Don't try to do too much in one formula: it makes it hard to debug and maintain, and may even make your worksheet calculate more slowly. You have lots of space on a worksheet so don't be afraid to use it by splitting the formula up!
    [2] Consider using descriptive named ranges to make your formulas easier to understand and (possibly) shorter.

  11. #11
    Join Date
    Oct 2009
    Posts
    3

    Thank you

    Mike, that was exactly what I was looking for, I knew it was simple.
    Thank you very much.

  12. #12
    Join Date
    Oct 2009
    Posts
    3
    Anantha, I will research if(iserror()) , I will probably need that also

    Colin, sounds like good advice (problem solving process)

    Thank you all for your input, it is greatly appreciated

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •