Formula Error

[Jack]

Can anyone tell me what is wrong with the following
formula ... I have two pull down boxes with 6 and 7
choices. I am trying to return certain values depending
on the selections. (i.e. Matrix)

=IF(AND(L6=2,L9=2),r3,IF(AND(L6=2,L9=3),r4,IF(AND
(L6=2,L9=4),r5,IF(AND(L6=2,L9=5),r6,IF(AND
(L6=2,L9=6),r7,IF(AND(L6=2,L9=7),r8,IF(AND
(L6=2,L9=8),r9,IF(AND(L6=3,L9=2),s3,IF(AND
(L6=3,L9=3),s4,IF(AND(L6=3,L9=4),s5,IF(AND
(L6=3,L9=5),s6,IF(AND(L6=3,L9=6),s7,IF(AND
(L6=3,L9=7),s8,IF(AND(L6=3,L9=8),s9,IF(AND
(L6=4,L9=2),t3,IF(AND(L6=4,L9=3),t4,IF(AND
(L6=4,L9=4),t5,IF(AND(L6=4,L9=5),t6,IF(AND
(L6=4,L9=6),t7,IF(AND(L6=4,L9=7),t8,(IF(AND
(L6=4,L9=8),t9,IF(AND(L6=5,L9=2),u3,IF(AND
(L6=5,L9=3),u4,IF(AND(L6=5,L9=4),u5,IF(AND
(L6=5,L9=5),u6,IF(AND(L6=5,L9=6),u7,IF(AND
(L6=5,L9=7),u8,IF(AND(L6=5,L9=8),u9,IF(AND
(L6=6,L9=2),v3,IF(AND(L6=6,L9=3),v4,IF(AND
(L6=6,L9=4),v5,IF(AND(L6=6,L9=5),v6,IF(AND
(L6=6,L9=6),v7,IF(AND(L6=6,L9=7),v8,IF(AND
(L6=6,L9=8),v9,IF(AND(L6=7,L9=2),w3,IF(AND
(L6=7,L9=3),w4,IF(AND(L6=7,L9=4),w5,IF(AND
(L6=7,L9=5),w6,IF(AND(L6=7,L9=6),w7,IF(AND
(L6=g,L9=7),w8,IF(AND
(L6=7,L9=8),w9,q2)))))))))))))))))))))))))))))))))))))))))
)

Thanks.

Jack

 

[Frank abel]

Hi
Excel only supports 7 nested function calls (you have many
more). It looks like you're returning cell references. You
may try the following approach instead:
1. create a separate sheet as lookup table. Name this
sheet for example 'lookup' and use the following layou:

A B C
1 2 9 R5
2 2 5 R6
3 2 6 R7
....

Now use the following array formula (entered with
CTRL+SHIFT+ENTER):
=INDIRECT(INDEX('lookup'!$C$1:$C$100,MATCH(1,('lookup'!
$A$1:$A$100=L6)*('lookup'!$B$1:$B$100=L9),0)))