Having a problem making IFERROR work in multipule IF statements

[Bobsone1]

HI.
We have Excel 2007 which is a German version so we replace commas ( , ) with semicolons ( ; ) (and there is some funny spelling )

I am a beginner and have just discovered IFERROR.

The following is the formula I am currently battling with;
=IFERROR(IF(DAY(Januar!AP$8)+1<>DAY(Januar!AQ$8);OFFSET(INDEX(Januar!$AQ$9:$AQ$35;MATCH($C20;Januar!$C$9:$C$35;0));0;-1;1;-6);"OOPS");"Oh Bugger")
This formula works well and I am impressed with the IFERROR function.

However, what I cannot figure out is how to have the IFERROR work when I string together multiple versions of my IF formula e.g.
=IFERROR(IF(DAY(Januar!AP$8)+1<>DAY(Januar!AQ$8);OFFSET(INDEX(Januar!$AQ$9:$AQ$35;MATCH($C20;Januar!$C$9:$C$35;0);0;-1;1;-6));IFERROR(IF(DAY(Januar!AQ$8)+1<>DAY(Januar!AR$8);OFFSET(INDEX(Januar!$AR$9:$AR$35;MATCH($C20;Januar!$C$9:$C$35;0));0;-1;1;-6);"P"));Bugger))

I need to have up to 7 variations of the formula all depending on the IF(DAY(Januar!AP$8)+1<>DAY(Januar!AQ$8) which I use to find the Cell where a month changes (i.e. 31st>1st, 28th>1st etc) from the end of a month to the beginning of the next.

Does anybody have an idea (or two)? If I have to use ISERROR the formula will be very very long.

Regards.

 

[Bobsone1]

Any ideas, hints, is my formula structured properly?
If it helps, I have uploaded an example of where the formula is intended to work (Februar J9-P35).

 

[NARAYANK991]

Hi ,

Your formula , which is :

=IF(DAY(Januar!AP$8)+1<>DAY(Januar!AQ$8),OFFSET(INDEX(Januar!$AQ$9:$AQ$35,MATCH($C13,Januar!$C$9:$C$35,0)),0,-1,1,-6),IF(DAY(Januar!AQ$8)+1<>DAY(Januar!AR$8),OFFSET(INDEX(Januar!$AR$9:$AR$35,MATCH($C13,Januar!$C$9:$C$35,0)),0,-1,1,-6),"Bugger"))

can be segmented as follows :

=IF( test1 , action if test1 is satisfied , IF( test2 , action if test2 is satisfied , "Bugger"))

If you evaluate the formula , you will see that the errors are stemming from the following segment :

MATCH($C13,Januar!$C$9:$C$35,0)

when a match is not found ; rather than use the IFERROR , which will trap all errors , you can use a better mechanism by trapping only the #N/A error , which the MATCH function returns when it does not find a match , by :

=IF (ISNA(MATCH($C13,Januar!$C$9:$C$35,0)) , "No Match Found" , IF(DAY(Januar!AP$8)+1<>DAY(Januar!AQ$8),OFFSET(INDEX(Januar!$AQ$9:$AQ$35,MATCH($C13,Januar!$C$9:$C$35,0)),0,-1,1,-6),IF(DAY(Januar!AQ$8)+1<>DAY(Januar!AR$8),OFFSET(INDEX(Januar!$AR$9:$AR$35,MATCH($C13,Januar!$C$9:$C$35,0)),0,-1,1,-6),"Bugger")))

Narayan

 

[Bobsone1]

Thanks for your reply Narayan.
Your solution works well, I was trying to remove Match (NA) errors.
I was rather confused with the IFERROR function. Although it worked well with one IF sequence, when I tried to have multiple IFs it always gave a "...you have entered to few/many functions..." alert. For me, it was a bit of a head scratcher.

Thanks again for your efforts:

Bob.