Error with Index + Match formula

  • Thread starter Thread starter Kristi
  • Start date Start date
K

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))))))
 
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
 
Back
Top