Nesting problem, Please Help

[yeidy927]

Hi,

I an trying to solve this nesting but cant. Below is the formula that tried to use but gives me the error for too much nests. Below the formula is the table with the values. I tried a VLOOKUp but didnt work, probably I was doing it wrong.


=IF(AND($D$4="AA",I7=1),10,IF(AND($D$4="AA",I7=2),18,
IF(AND($D$4="AA",I7=3),38,IF(AND($D$4="AA",I7=4),51,
IF(AND($D$4="BB",I7=1),10,IF(AND($D$4="BB",I7=2),27,
IF(AND($D$4="BB",I7=3),38,IF(AND($D$4="BB",I7=3),46,
IF(AND($D$4="CC",I7=1),10,IF(AND($D$4="CC",I7=2),0,
IF(AND($D$4="CC",I7=3),35,IF(AND($D$4="CC",I7=4),51,
IF(AND($D$4="DD",I7=1),10,IF(AND($D$4="DD",I7=2),25,
IF(AND($D$4="DD",I7=3),55,IF(AND($D$4="DD",I7=4),0))))))))))))))))

TYPE	AA	BB	CC	DD
1	10	10	10	10
2	18	27	0	25
3	38	38	35	55
4	51	46	51	0

<tbody>
</tbody>

 

[63falcondude]

Is this what you are after?

Excel 2010
	A	B	C	D	E	F	G	H	I
1	TYPE	AA	BB	CC	DD		Type	Letters	Value
2	1	10	10	10	10		4	BB	46
3	2	18	27	0	25
4	3	38	38	35	55
5	4	51	46	51	0
Sheet1

Cell Formulas
Range	Formula
I2	=SUMPRODUCT(($A$2:$A$5=G2)*($B$1:$E$1=H2)*($B$2:$E$5))

 

[63falcondude]

You can also use:

=INDEX($B$2:$E$5,MATCH(G2,$A$2:$A$5,0),MATCH(H2,$B$1:$E$1,0))

referring to the layout in post #2.

 

[Norie]

You could use INDEX/MATCH, for example I put your little table in K1:O5 and then used this.

=INDEX(L2:O5,MATCH(I7,K2:K5,0),MATCH(D4,L1:O1,0))

 

[yeidy927]

This worked perfectly!!! thank you!