I created the following formula (which does not seem to work...)

{=CELL("address",INDEX(J$8:J$65000,MATCH(1,(J$8:J$ 65000<K
$6)*(ISNUMBER(J$8:J$65000)),0)))}

Where the original data is in Col. J (the data in this column is
expressed in cumulative percentage form) and the reference value is in
cell K6 (expressed as a percentage, in my case the value is
"80.00%"). What I get as a result is always "$J$8" which is not the
cell the has the highest cumulative percentage value that is lower
than 80%.

To be clear, J8 has 2.23%, J9 has 4.18%, J10 has 5.91%,... J652 has
79.98% and J653 has 80.01%. When applying the above-mentioned
formula, I would expect the result to be "$J$652". Instead, I keep on
getting "$J$8".

Thanks for all your help.
--
tb

On Dec 12, 5:42 pm, "Peo Sjoblom" wrote:
=MATCH(1,(P1:P500<80)*(ISNUMBER(P1:P500)),0)

entered with ctrl +shift & enter

will give you the row number

=INDEX(P1:P500,MATCH(1,(P1:P500<80)*(ISNUMBER(P1:P 500)),0))

entered the same way will give you the value

=CELL("address",=INDEX(P1:P500,MATCH(1,(P1:P500<80 )*(ISNUMBER(P1:P500)),0)))

entered the same way will give you the address as a text string

--

Regards,

Peo Sjoblom

"tb" wrote in message

...

Is there a way to find the cell address where a specific value is?
If I wanted to know in which row (of column P) I have the greatest
value that is <= 80, how would I do that?
Thanks.
--
Tiziano