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

=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

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

Thanks, Peo!
--
tb

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

(Formulas shown in sections for readability)

Try this regular formula:
=CELL("address",INDEX(J8:J65000,MATCH(MAX(
INDEX((J8:J65000<K6)*J8:J65000,0)),J8:J65000,0)))

or...this ARRAY FORMULA version,committed with CTRL+SHIFT+ENTER:
=CELL("address",INDEX(J8:J65000,MATCH(MAX(
(J8:J65000<K6)*J8:J65000),J8:J65000,0)))

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"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

On Dec 12, 6:50 pm, "Ron Coderre"
wrote:
(Formulas shown in sections for readability)

Try this regular formula:
=CELL("address",INDEX(J8:J65000,MATCH(MAX(
INDEX((J8:J65000<K6)*J8:J65000,0)),J8:J65000,0)))

or...this ARRAY FORMULA version,committed with CTRL+SHIFT+ENTER:
=CELL("address",INDEX(J8:J65000,MATCH(MAX(
(J8:J65000<K6)*J8:J65000),J8:J65000,0)))

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"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

Thanks, Ron! It works very well.
--
tb