Results 1 to 12 of 12

051109, 09:21 #1Registered User
 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!

051109, 15:29 #2Registered User
 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),)
Code:=IF(ISNA(MATCH(B16,'Number of Accruals'!A2:A1014,0)),,VLOOKUP(B16,'Number of Accruals'!A2:F1014,6,FALSE))

051209, 06:35 #3Registered User
 Join Date
 Apr 2006
 Posts
 65
Hi Colin,
the second code returns a '0' even if there is a value in the range?

051209, 07:42 #4Registered User
 Join Date
 Apr 2004
 Location
 Derbyshire, UK
 Posts
 788
Provided Answers: 1Hi
I think this
Code:=IF(ISERROR(VLOOKUP(B16,'Number of Accruals'!A2:F1014,6,FALSE)),,VLOOKUP(B16,'Number of Accruals'!A2:F1014,6,FALSE))
Code:=IF(ISNA(VLOOKUP(B16,'Number of Accruals'!A3:F1015,6,FALSE)),,VLOOKUP(B17,'Number of Accruals'!A3:F1015,6,FALSE))
MTB

051209, 08:40 #5Registered User
 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!!!

051209, 15:04 #6Registered User
 Join Date
 Sep 2008
 Location
 London, UK
 Posts
 511
Originally Posted by waylander

102609, 21:35 #7Registered User
 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

102709, 01:25 #8Registered User
 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(...)

102709, 04:04 #9Registered User
 Join Date
 Apr 2004
 Location
 Derbyshire, UK
 Posts
 788
Provided Answers: 1Originally Posted by pete.ford
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

102709, 05:03 #10Registered User
 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.

102709, 06:23 #11Registered User
 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.

102709, 06:37 #12Registered User
 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