Complicated IF Formula.

Atm I am trying to get the following formula to work:

=IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=7,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),M$2:N$10002,2),IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=6,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),L$2:N$10002,3),IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=5,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),K$2:N$10002,4),IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=4,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),J$2:N$10002,5),"ABC"))))

I wont bore you with what i am trying to acheive & why, but the last part of the formula there is a parathesis error apparently. Even though the Brackets all match up imo. ]=

when building IF formulas I usually leave the last error part as "ABC" which i then ichange when entering the next stage.

FYI: =IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=7,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),M$2:N$10002,2),IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=6,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),L$2:N$10002,3),IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=5,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),K$2:N$10002,4),"ABC"))) works. however when changing the "ABC" here, trying to insert:

IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))=4,VLOOKUP(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),J$2:N$10002,5),"Check Manual")

I get my error msg,..,. pls someone tell me whats going wrong I Hva ebeen staring at this for hours.

Ty
SP

Not sure, but it is quite possible that you have hit the character length limit for an in-cell formula.

You can try creating names with the refers-to being some of these formulas, then use the names instead. That might be a work-around.

Meanwhile, I'll check references for the character length limit.

Seems I was wrong, but close.

Got text below from http://blogs.msdn.com/excel/archive/...26/474258.aspx, where they are comparing limits in Excel 2003 to limits in Excel 2007.

Probably you have hit the limit on nesting of formulas ...

The maximum length of formulas (in characters)
Old Limit: 1k characters
New Limit: 8k characters

The number of levels of nesting that Excel allows in formulas
Old Limit: 7
New Limit: 64

By naming parts of your formulae. Check out this link :
http://cpearson.com/excel/named.htm
The part on " naming formulas"

Usually if you have a repeating pattern in your formula there's an easier way. Try

=IF(INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))>3,INDEX(N$2:N$10002,MATCH(MID($A31,1,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))),INDEX(J$2:M$10002,0,INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))-3),0)),"ABC")

Or possibly easier to maintain if you split into 2 cells e.g. in P2

=INDEX($B$2:$E$2,MATCH(MIN(B31:E31),B31:E31,0))

then in another cell

=IF(P2>3,INDEX(N$2:N$10002,MATCH(LEFT($A31,P2),INDEX(J$2:M$10002,0,P2-3),0)),"ABC")