Error with Index + Match formula

[Kristi]

I am having trouble with the 7th match. Excel will not accept it. Ca
someone help with this problem? I have included the formula i
question.

Thanks, Kristi

=IF(I5="LOW",INDEX(A:Z,MATCH(D4,A:A,0),MATCH(D3,52:52,0)),IF(I5="LOW/FAIR",INDEX(A:Z,MATCH(D4,A:A,1),MATCH(D3,76:76,1)),IF(I5="FAIR",INDEX(A:Z,MATCH(D4,A:A,1),MATCH(D3,100:100,1)),IF(I5="FAIR/AVG",INDEX(A:Z,MATCH(D4,A:A,1),MATCH(D3,124:124,1)),IF(I5="AVG",INDEX(A:Z,MATCH(D4,A:A,1),MATCH(D3,148:148,1)),IF(I5="AVG/GOOD",INDEX(A:Z,MATCH(D4,A:A,1),MATCH(D3,172:172,1)),IF(I5="GOOD",INDEX(A:Z,MATCH(D4,A:A,1),MATCH(D3,194:194,1)),0))))))

 

[Frank Kabel]

Hi
you have exceeded the maximum of 7 nested functions in one formula. No
way around this. I would try the following workaround in your case:
1. set up a separate sheet with a lookup table that links your value
from I5 to the row index. Lest call this sheet 'lookup with the
following layout:
A B
1 FAIR 52
2 LOW/FAIR 76
....

Now you may try the following formula
=INDEX(A:Z,MATCH(D4,A:A,0),MATCH(D3,INDIRECT(VLOOKUP(I5,'lookup'!$A$1:$
B$20,2,0)&":"&VLOOKUP(I5,'lookup'!$A$1:$B$20,2,0)),0))

You may have to adapt the 3rd parameter of the MATCH function as you
used '0' and '1' in your original formula