How to get the reference of the result of an array formula in a list?

Question

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.

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

Answer 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:

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

Answer 2

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

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