Finding the Address of a Cell

[tb]

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.

 

[Peo Sjoblom]

=MATCH(1,(P1500<80)*(ISNUMBER(P1500)),0)

entered with ctrl +shift & enter

will give you the row number

=INDEX(P1500,MATCH(1,(P1500<80)*(ISNUMBER(P1500)),0))

entered the same way will give you the value


=CELL("address",=INDEX(P1500,MATCH(1,(P1500<80)*(ISNUMBER(P1500)),0)))

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


--


Regards,


Peo Sjoblom

 

[tb]

=MATCH(1,(P1500<80)*(ISNUMBER(P1500)),0)

entered with ctrl +shift & enter

will give you the row number

=INDEX(P1500,MATCH(1,(P1500<80)*(ISNUMBER(P1500)),0))

entered the same way will give you the value

=CELL("address",=INDEX(P1500,MATCH(1,(P1500<80)*(ISNUMBER(P1500)),0)))

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

--

Regards,

Peo Sjoblom

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.

 

[Ron Coderre]

(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]

(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)

Thanks, Ron! It works very well.