1. Registered User
Join Date
Apr 2006
Posts
65

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.

2. Registered 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),)`
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. Registered User
Join Date
Apr 2006
Posts
65
Hi Colin,
the second code returns a '0' even if there is a value in the range?

4. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
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. Registered 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!!!

6. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
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. Registered 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

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

9. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
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. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511

[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. Registered 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.

12. Registered 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

#### Posting Permissions

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