IF + OR formula using ISNA

**waternut** · 06-21-2013, 02:16 PM

Hi All,

I am working in a program called Xcelsius which does not accept the "IFERROR" formula, as a work around I am using "ISNA" I guess the best way to indicate what I am attempting to do is to write it out in english since apparently I can't get the formula to work

In the attached in cell A21 I select a particular organization and what I need to happen is that if there is data in the area of A4:P17 for that particular org for that particular month give me the data based upon an HLOOKUP, but if there is no data leave it blank. What I am getting stumped on is that in addition to this formula I also need it to leave cells blank if that month is not available. In my example I only have data through the month of May so if there is no data I need that to be blank as well.
For this example if the formula was working correctly, I would envision that when I select “AJ” in cell A21 I would get blanks for every month since there is no actuals, however if I select “NE” in A21 and my month was toggled to April (7th month in a fiscal year) I would see actuals through April and blanks for May through September. I seem to have the first part of it working the (the NE piece) but when I toggle AJ, I only get #N/A.

Any assistance is appreciated.

Thx.

Chris

**p24leclerc** · 06-21-2013, 03:07 PM

I think you have to change your formula in cell B22 to this one and copy along your columns

Please Login or Register  to view this content.

**waternut** · 06-24-2013, 10:20 AM

Thanks P24leclerc!!! This worked, just in reverse. As indicated this formula will return blanks for months through April and then give me values for May through Sept. I just changed the greater than to a less than and it worked perfect!!!
This is what I ended up with, IF(OR($I$1<(B$20-3),ISNA(HLOOKUP($A$21,$A$4:$Q$17,B19,FALSE))),"0",HLOOKUP($A$21,$A$4:$Q$17,B19,FALSE))