Index Formula using max and excluding specifc value

I need to find the MAX value in a range that excludes a specific value (100% in this case) and return the value of the row above it.


1st row has the values of: Y, A, D, DV, I, E
2nd row will have different % such as: 100%, 25%, 100%, 10%, no value, 50%

What is need is to find the MAX value excluding any that say 100% and return the value to the row above it. So in this example the result should be "E" as 50% is the MAX number of all the values excluding the 100% (which there are 2 of them in this example) and the "E" was the value above the 50%.

Below is the formula I started with but cannot figure out why is keeps returning the wrong value that I would like. I also tried it by using the CTRL+Shift+Enter, but it does the same thing. I might be making more difficult that it really needs to be.

=INDEX(G7:L7,MATCH(MAX(IF(OR(G9<100,H9<100,I9<100,J9<100,K9<100,L9<100),G9:L9)),G9:L9,0))

Any help would be great! Thanks...B

try it
by using the CTRL+Shift+Enter

=INDEX($G$7:$L$7,MATCH(MAX(IF(G9:L9<1,G9:L9)),G9:L9,0))

Wonderful. This worked. Thank a million.

I'll be sure to upload the file next time.

:-)