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!

Hi waylander,

If you are using Excel 2007 you can use this:
If you are using an earlier version than Excel 2007 you can use this:
Hope that helps..

Hi Colin,
the second code returns a '0' even if there is a value in the range?

Hi

I think this

or this
Should do it ??

MTB

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!!!

No worries, glad you got it working.

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 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(...)

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

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.

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

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