Could someone explain formula (Lookup/frequency/mmult)?

[Rolle88]

Hello!

I did a lot of work using excel a while back, but I got stuck doing some basic stuff for a long time which caused most of my knowledge to go down the drain as I wasn't using it... And I'm now trying to refresh my memory on how to use some of the more advanced formulas, and I came across one that I simply can't wrap my head around.
It's a formula that calculates the shortest distance between two matrix's with coordinates.

=LOOKUP(1,1/FREQUENCY(0,MMULT((B$1:C$10-E1:F1)^2,{1;1})),A$1:A$10)

I know that the range B:C contains the different positions that is being matched against E1:F1, and that A1:A10 is the name/value that is returned.
But I can't for the life of me now remember why I used this one, and how it actually works. I found the same formula on a websearch (which is probably where I found it in the first place)

Could someone break it down for me what the different parts does? as I would like to know how this thing actually works.

The source of the formula: worksheet function - Find the nearest set of coordinates in Excel - Stack Overflow

 

[Rolle88]

Or the things I'm really interested in is this part:
MMULT((B$1:C$10-E1:F1)^2,{1;1}))
as I can't figure out what {1;1} does in the formula

 

[pgc01]

Hi
Welcome to the board

When you calculate the distance between 2 points you add the squares of the difference in X and Y.

(B$1:C$10-E1:F1)^2

Gives you those squares of the differences in terms of a nx2 matrix, like

2,5
3,1
5,8
... etc

You use the matrix multiplication to add each row, so that you get

7
4
13
... etc

that are the squares of the distances.

You'll then find the minimum of those values.

 

[Rolle88]

Okey thanks, that helps a bit
But what's the point of {1;1}?

Feeling a bit think right now, as I do understand the Sqrt((x1-x2)^2*(y1-y2)^2) for normally calculating distances, but that {1;1} is just confusing right now, as I can't find any other example where the mmult formula is used like this

 

[pgc01]

Hi

If you remember the rules of matrix multiplication you know that the matrices can only be multiplied if the number of columns of the first matrix is equal to the number of rows of the second matrix.
The {1;1} is a vertical array that is multiplied by the nx2 matrix and adds the elements of each row.

 

[pgc01]

Remark: If you don't remember how to work with matrices you can also just add the values:

=LOOKUP(1,1/FREQUENCY(0,(B$1:B$10-E1)^2*(C$1:C$10-F1)^2),A$1:A$10)