IF statement nesting levels

**rod642** · 06-24-2013, 01:46 PM

=IF(ISNA(VLOOKUP(Master!A2,RockSlopes!$AE$4:$AI$253,4,FALSE)),IF(ISNA(VLOOKUP(Master!A2,'2ndEastMains'!$AE$4:$AK$250,4,FALSE)),IF(ISNA(VLOOKUP(Master!A2,'1stEastMains'!$AE$4:$AK$250,4,FALSE)),IF(ISNA(VLOOKUP(Master!A2,'1stSouthMains'!$AE$4:$AK$250,4,FALSE)),IF(ISNA(VLOOKUP(Master!A2,SouthBreakout!$AE$4:$AK$250,4,FALSE)),IF(ISNA(VLOOKUP(Master!A2,WestBreakout!$AE$4:$AK$250,4,FALSE)),IF(ISNA(VLOOKUP(Master!A2,Sump!$AE$4:$AI$250,4,FALSE)),"",VLOOKUP(Master!A2,Sump!$AE$4:$AI$250,4,FALSE)),VLOOKUP(Master!A2,WestBreakout!$AE$4:$AK$250,4,FALSE)),VLOOKUP(Master!A2,SouthBreakout!$AE$4:$AK$250,4,FALSE)),VLOOKUP(Master!A2,'1stSouthMains'!$AE$4:$AK$250,4,FALSE)),VLOOKUP(Master!A2,'1stEastMains'!$AE$4:$AK$250,4,FALSE)),VLOOKUP(Master!A2,'2ndEastMains'!$AE$4:$AK$250,4,FALSE)),VLOOKUP(Master!A2,RockSlopes!$AE$4:$AI$253,4,FALSE))

My main problem is the nesting levels of my statement; I keep getting a compatibility error when I save my file due to the above function. It actually only has 7 levels, unless I am way off on how the nesting works.

Is there another option or formula that will serve the same purpose?
I have tried to replace some of it with a range of sheets but must be doing something wrong.

I need a formula re-write or something!

Also, this has been entered as an array in my sheet.

Thanks
rod642

**dilipandey** · 06-30-2013, 03:02 PM

HI rod642,

We can use a data table layout to refer the If logics using some look funtion... can you upload a sample workbook and the clear description of your logic? thanks.

Regards,
DILIPandey
<click on below * if this helps>

**FDibbins** · 06-30-2013, 03:22 PM

Your profile says 2007, which does not have the 7-level restriction...and that restriction would not even let you enter, if it was a factor.\

Also, I dont see why it needs to be array-entered?

Are you trying to save as 2003 (.xls)?

Because you are using 2007, you can simplify that formula to this...

=IFerrorA(VLOOKUP(Master!A2,RockSlopes!$AE$4:$AI$253,4,FALSE),
IFerror(VLOOKUP(Master!A2,'2ndEastMains'!$AE$4:$AK$250,4,FALSE),
IFerror(VLOOKUP(Master!A2,'1stEastMains'!$AE$4:$AK$250,4,FALSE),
Iferror(VLOOKUP(Master!A2,'1stSouthMains'!$AE$4:$AK$250,4,FALSE),
IFerror(VLOOKUP(Master!A2,SouthBreakout!$AE$4:$AK$250,4,FALSE),
IFerror(VLOOKUP(Master!A2,WestBreakout!$AE$4:$AK$250,4,FALSE),
IFerror(VLOOKUP(Master!A2,Sump!$AE$4:$AI$250,4,FALSE),"")
(I think I got the () and , right)

**rod642** · 09-16-2013, 02:15 PM

Everything seems to be working satisfactory right now. Thanks for all the help. I would like to know what I have to do to have the worksheet names in a range, and if that will simplify things for me. I read on another post at some time that it is possible, but can't seem to get anything to work. At this point, I have several workbooks and up to 7 worksheets in each to accomplish my task. I would like to get back down to 1 workbook.

**rod642** · 01-23-2014, 10:53 AM

Everything has been working fine and at a satisfactory level. Excuse the long delay in letting anyone know, was testing and using what I have to be sure it will work efficiently.