Problem debugging formula

This may have been better to do in VB but that is beyond my feeble skills.

I'm using IF and VLOOKUP to choose an appropriate rate dependent on a variable quantity (in this case, sheets of material run through a variety of printing and associated processes). I've got this formula, long and ugly, but it should work. Paranthenses all seem to match up but when I cut and paste it (from Word, coz I can uses colours to match everything up etc), Excel tell me there is an error, and places the cursor at the end of the second AND test between ProcessTable,7)) and the subsequent comma.

Here's the formula:

=IF($C25<>"", IF(VLOOKUP($C25,ProcessTable,6)=100000, VLOOKUP($C25,ProcessTable,5), IF(AND(SheetsA >=VLOOKUP($C25,ProcessTable,4), SheetsA <VLOOKUP($C25,ProcessTable,6)), VLOOKUP($C25,ProcessTable,5)), IF(AND(SheetsA >=VLOOKUP($C25,ProcessTable,6), SheetsA <VLOOKUP($C25,ProcessTable,8)), VLOOKUP($C25,ProcessTable,7)), IF(SheetsA>=VLOOKUP($C25,ProcessTable,8), VLOOKUP($C25,ProcessTable,9))),”not valid”)

You can see part of the lookup table (ProcessTable) at:
http://www.number64.com.au/temp/excel.htm

Thanks!

bloody brackets. thanks man.

I note that none of your VLOOKUPs has a 4th argument. This could cause problems if C25 contained, say, 15 by mistake instead of 5, the VLOOKUP would return values based on the highest value lower than 15, i.e. 9 in your example, or is that what you want to happen?

You could also shorten the formula, i.e.

=IF($C25<>"",VLOOKUP($C25,processtable,IF(OR (VLOOKUP($C25,processtable,6)=100000,AND(SheetsA >=VLOOKUP($C25,processtable,4),SheetsA<VLOOKUP($C25,processtable,6))),5,IF(AND(SheetsA >=VLOOKUP($C25,processtable,6),SheetsA<VLOOKUP($C25,processtable,8)),7,IF(SheetsA>=VLOOKUP($C25, processtable,8),9)))),"not valid")