help with distance array

[grime]

I am calculating distances between 2 points.

Column A contains my 'X' location
Column B contains my 'Y' location
Column C contains the distance of the closest point:
{=SMALL(SQRT(POWER((H2-H2:H10),2)+POWER((I2-I2:I10),2)),2)}
Column D contains the distance to the 2nd closest point:
{=SMALL(SQRT(POWER((H2-H2:H10),2)+POWER((I2-I2:I10),2)),3)}
Column E contains the distance to the 3rd closest point:
{=SMALL(SQRT(POWER((H2-H2:H10),2)+POWER((I2-I2:I10),2)),4)}
and so on...

My problem is, although I can find the distances to those location
(rows), I can't figure out how to find which location is refers to.
So take column C for example...if my distance is 1.35...how do I fin
out the other location that the 1.35 came from?

Thanks in advance

 

[Ron Rosenfeld]

I am calculating distances between 2 points.

Column A contains my 'X' location
Column B contains my 'Y' location
Column C contains the distance of the closest point:
{=SMALL(SQRT(POWER((H2-H2:H10),2)+POWER((I2-I2:I10),2)),2)}
Column D contains the distance to the 2nd closest point:
{=SMALL(SQRT(POWER((H2-H2:H10),2)+POWER((I2-I2:I10),2)),3)}
Column E contains the distance to the 3rd closest point:
{=SMALL(SQRT(POWER((H2-H2:H10),2)+POWER((I2-I2:I10),2)),4)}
and so on...

My problem is, although I can find the distances to those locations
(rows), I can't figure out how to find which location is refers to.
So take column C for example...if my distance is 1.35...how do I find
out the other location that the 1.35 came from?

Thanks in advance,

Something like:

x =INDEX(A:A,MATCH(1.35,C:C,0))
y =INDEX(B:B,MATCH(1.35,C:C,0))


--ron

 

[grime]

Thanks for the reply Ron, but that doesn't get me what I am lookin
for...

That gives me the coords of the original location... I need to find th
coords for the location that it found to be the closest

 

[Bernie Deitrick]

Why

{=SMALL(SQRT(POWER((H2-H2:H10),2)+POWER((I2-I2:I10),2)),2)}

and not

{=SMALL(SQRT(POWER((A2-H2:H10),2)+POWER((B2-I2:I10),2)),2)}

?

Anyway, you can use a variation of this array formula: (these are both for the fourth closest point)

To return the value from H:
=INDEX(H:H,MAX((SMALL(SQRT(POWER(($H2-$H$2:$H$10),2)+POWER(($I2-$I$2:$I$10),2)),4)=SQRT(POWER(($H2-$H$2:$H$10),2)+POWER(($I2-$I$2:$I$10),2)))*ROW($H$2:$H$10)))

To return the value from I:
=INDEX(I:I,MAX((SMALL(SQRT(POWER(($H2-$H$2:$H$10),2)+POWER(($I2-$I$2:$I$10),2)),4)=SQRT(POWER(($H2-$H$2:$H$10),2)+POWER(($I2-$I$2:$I$10),2)))*ROW($H$2:$H$10)))

HTH,
Bernie
MS Excel MVP

 

[grime]

Works perfectly. Yer the man!