IF problem with ISERROR 1

[mpm32]

I have cells in one sheet that when using this formula, looks up values in another sheet called alldata. alldata is linked to an access database.

I am using this formula

=IF(ISERROR(VLOOKUP((CONCATENATE($A$3,$A11,D$6)),alldata!$A$3
:$D$5001,4,FALSE)),0,VLOOKUP((CONCATENATE($A$3,$A11,D$6)),
alldata!$A$3:$D$5001,4,FALSE))

This works fine.

I would like to add another condition.

In the alldata sheet I have a cell called period ending. In this example I have 007 in that cell.

I would only like to return the value for the formula above if the period at the top of the column is less than the period ending in the alldata cell. If that is false, to return a value from elsewhere on the same sheet.

I tried this;

IF(D6<alldata!$A$3,IF(ISERROR(VLOOKUP((CONCATENATE($A$3,$A11
,D$6)),alldata!$A$3:$D$5000,4,FALSE)),0,VLOOKUP((CONCATENATE
($A$3,$A11,D$6)),alldata!$A$3:$D$5000,4,FALSE),AG11)))

I am getting a too many arguments error. I think it has to do with the second IF(ISERROR portion of the formula but I can't figure out how to correct it. I thought you could nest IF statements but I think the ISERROR is messing that up.

Any suggestions?

In advance, thanks for your help.

 

[SkipVought]

Hi,

In general, work from the inside out. In the Formula Bar, you can SELECT an expression and EVALUATE the result by hitting F9. Once you are done, hit ESC to return the expression. If you get an NA or REF error using this procedure, then you know EXACTLY where to look to debug your error.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mpm32]

Excellent, I like that error checking method.

However, I checked each piece of the formula and I get proper results for each section.

For the total formula I am getting a #VALUE error as the result.

Not sure what to look for next.

 

[SkipVought]

have you checked HELP on what to do with this specific error?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mpm32]

Ha you're making me work for this, but it's good, I'm learning a lot of stuff here.

I'm into the evaluate formula on the error box.

In evaluate formula I get this;

IF(TRUE,(66459.93,AG11))

with the message "The next evaluation will result in an error"

When I click Evaluate I get;

IF(TRUE,(#VALUE!))

But I still don't see my mistake.

 

[SkipVought]

What's in AG11?

Which expression are you evaluating?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mpm32]

In AG11 - a value of 73,787.

I was evaluating the entire expression.

So in this case, it's true that the period is less than the ending period in alldata. Therefore, it should run the vlookup expression. But from there I'm stuck.

It has to be the second IF but I don't know how to fix it.

 

[SkipVought]

You still did not tell me WHAT EXPRESSION you were eveluating.

Please post the entire expression

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mpm32]

I was evaluating this;

IF(D6<alldata!$A$3,IF(ISERROR(VLOOKUP((CONCATENATE($A$3,$A11
,D$6)),alldata!$A$3:$D$5000,4,FALSE)),0,VLOOKUP((CONCATENATE
($A$3,$A11,D$6)),alldata!$A$3:$D$5000,4,FALSE),AG11)))

 

[SkipVought]

The WHOLE thing or just PART?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mpm32]

Well I ran through the whole thing.

Prior to the error, up to this step I get;

IF(TRUE,(IF(FALSE,#N/A,66459.93),AG11))

then;

IF(TRUE,(66459.93,AG11))

with the message "The next evaluation will result in an error"

When I click Evaluate I get;

IF(TRUE,(#VALUE!))

 

[mpm32]

Ha! I found it. The extra ( before the second IF was not needed.

Thanks for your help and thanks for the new tools I'll be sure to use in the future.