2

I've got two arrays, each one includes shop ids, x coordinates and y coordinates for us and the competition.

I'm looking to get for each shop of ABC corp, its closest competitor in list XYZ corp.

enter image description here

So far, I've managed to get the distance between each ABC shop and its closest XYZ comp, with an array formula in col. H :

'{MIN((sqrt((Power(B3-$F$3:$F$6;2)+(power(C3-$G$3:$G$6;2))))))}

I'm stuck in finding for each ABCi the name XYZi (or line reference) corresponding to the calculated value in H.

I've been fiddling for two hours with Match()and Index() –as suggested in various similar questions on SE– and googling all I can think of but to no avail.

(I seem to recall that for XY coordinates on a spheroid the formula for distance between two points on a map might be different from the one used here, but for my real life purpose it's good enough.)

1

I think the array formula may just be complicating matters. An actual array could show all the distances apart =SQRT((E$2-$B4)^2+(E$3-$C4)^2) and then:

=CHOOSE(MATCH(MIN(E4:H4),$E4:$H4,0),E$1,F$1,G$1,H$1)

to pick the one that is closest in each case:

SU608775 example

Or to highlight the minimum for each row, you could use Conditional Formatting with the formula =E4=MIN($E4:$H4).

  • no that's perfect. I'm here to learn, and your answer was just the step I needed to be able to learn how to answer the problem. I know now how to properly use Index, Match, CHoose and array formulas, all at once. I'll post the answer in a few minutes. – P. O. Jun 18 '13 at 15:18
0

Starting from @pnuts solution, we can skip the array representation.

To get the min distance between two points on a grid between two coordinates and a set (array formula ctrl + Shift + Enter):

'={MIN((sqrt((Power(B3-$F$3:$F$6;2)+(power(C3-$G$3:$G$6;2))))))}

To skip the array display we use the distance calculation (without the MIN) as the second argument for MATCH(). It'll return the index position of the value within the array.

Then CHOOSE to get the k member within the list E3:E6 which is the origin of the minimum value :

{CHOOSE(MATCH(MIN((sqrt((B3-$F$3:$F$6)^2+((C3-$G$3:$G$6)^2))))),
(sqrt(((B3-$F$3:$F$6)^2+((C3-$G$3:$G$6)^2)))),0),$E$3;$E$4;$E$5:$E$6)}

As it is an array formula, it has to be validated with ctrl + Shift + Enter

enter image description here

The only thing left is to find a way around the 255 individual values limitations of the CHOOSE formula for list above this range.

(The formula to get the distance between two XY coordinates on a spheroid is different from the one used here, but for a small 2/3 lat. long. degrees area it's close enough.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.