combination of IF, INDEX/MATCH command

**zombie** · 11-19-2010, 12:07 PM

The code below is not working for me and I don't know why.

=IF(E30=2.7,INDEX(AA10:AA14,MATCH(E32,X10:X14,0)),IF(E30=3,INDEX(AA15:AA19,MATCH(E32,X15:X19,0)),IF(E30=3.3,INDEX(AA20:AA24,MATCH(E32,'X20:X24,0)),IF(E30=3.5,INDEX(AA25:AA29,MATCH(E32,X25:X29,0)),IF(E30=3.7,INDEX(AA30:AA34,MATCH(E32,X30:X34,0)),IF(E30=4,INDEX(AA35:AA39,MATCH(E32,X35:X39,0)),IF(E30=4.2,INDEX(AA340:AA44,MATCH(E32,X40:X44,0)),IF(E30=4.5,INDEX(AA45:AA49,MATCH(E32,X45:X349,0)),""))))))))

If I shorten it to the following i(take out the last to INDEX/MATCH commands) it works fine:

=IF(E30=2.7,INDEX(AA10:AA14,MATCH(E32,X10:X14,0)),IF(E30=3,INDEX(AA15:AA19,MATCH(E32,X15:X19,0)),IF(E30=3.3,INDEX(AA20:AA24,MATCH(E32,'X20:X24,0)),IF(E30=3.5,INDEX(AA25:AA29,MATCH(E32,X25:X29,0)),IF(E30=3.7,INDEX(AA30:AA34,MATCH(E32,X30:X34,0)),IF(E30=4,INDEX(AA35:AA39,MATCH(E32,X35:X39,0)),IF(E30=4.2,4.2,IF(E30=4.5,4.5,""))))))))

but I'd like to be able to call up values for "4.2" and "4.5" as well. Anybody have any suggestions? Thanks for the help.

**shg** · 11-19-2010, 12:41 PM

You can only nest 7 levels deep in Excel 2003.

You could change it to something like,

=CHOOSE(MATCH(E30, {2.7,3,3,3,3.7,3.7,4,4.2}, 0),
INDEX(AA10:AA14, MATCH(E32, X10:X14, 0)),
INDEX(AA15:AA19, MATCH(E32, X15:X19, 0)),
...

**NBVC** · 11-19-2010, 12:46 PM

If you setup a table of values where you list the possible E30 values, and in next 2 columns, the corresponding AA range and X range,

e.g. in I1: 2.7, in J1: AX10:AX14, in K1:X10:X14 and continue in I2:K2, etc...

then you can use:

Please Login or Register  to view this content.

where I1:K10 contains the table of values.

**rgi** · 11-19-2010, 12:52 PM

I found neither work as there is a stray apostrophe at E32,'X20:X24 just after E30=3.3

**sailepaty** · 11-19-2010, 01:12 PM

One more,

=INDEX(OFFSET($AA$1,5*MATCH(E30,{0,2.7,3,3,3,3.7,3.7,4,4.2}, 0)-1,0,5,1),MATCH(E32,OFFSET($X$1,5*MATCH(E30,{0,2.7,3,3,3,3.7,3.7,4,4.2}, 0)-1,0,5,1),0))

Or you can use an auxiliary cell, les't say E31.
=5*MATCH(E30,{0,2.7,3,3,3,3.7,3.7,4,4.2}, 0)-1

Then
=INDEX(OFFSET($AA$1,E31,0,5,1),MATCH(E32,OFFSET($X$1,E31,0,5,1),0))

Reagrds

**rgi** · 11-19-2010, 01:48 PM

Check out this substitute formula example as an alternative to your long IF statement. You can use the Indirect function to create cell address's on the fly, or from a table in this case. If you only have the single formula then debug what you have. (The error is likely a comma misplacement or paren misplacement. If you have lots of this formula, use the version I provided to speed the calculations and reduce memory requirements.

**sailepaty** · 11-19-2010, 02:14 PM

One more no volatile

E31=5*MATCH(E30,{0,2.7,3,3.3,3.5,3.7,3.7,4,4.2},0)

Then
=INDEX(INDEX(AA:AA,E31):INDEX(AA:AA,E31+4),MATCH(E32,INDEX(X:X,E31):INDEX(X:X,E31+4),0))

Or if the ranges could be in different columns or rows than multiple of 5.

E31=MATCH(E30,{2.7,3,3.3,3.5,3.7,3.7,4,4.2},0)

Then
=INDEX(INDEX((AA10:AA14,AA15:AA19,AA20:AA24,AA25:AA29,AA30:AA34,AA35:AA39,AA40:AA44,AA45:AA49),0,0,E31),MATCH(E32,INDEX((X10:X14,X15:X19,X20:X24,X25:X29,X30:X34,X35:X39,X40:X44,X45:X49),0,0,E31),0))

Regards

**zombie** · 11-23-2010, 10:49 AM

Originally Posted by shg

You can only nest 7 levels deep in Excel 2003.

You could change it to something like,

=CHOOSE(MATCH(E30, {2.7,3,3,3,3.7,3.7,4,4.2}, 0),
INDEX(AA10:AA14, MATCH(E32, X10:X14, 0)),
INDEX(AA15:AA19, MATCH(E32, X15:X19, 0)),
...

Thanks. This worked. I appreciate the help.