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

#### Rolle88

##### New Member
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

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Rolle88

##### New Member
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

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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.

Last edited:

#### pgc01

##### MrExcel MVP
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)

Replies
1
Views
1K
Replies
1
Views
71
Replies
5
Views
245
Replies
1
Views
122
Replies
3
Views
1K Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,163,652
Messages
5,832,928
Members
430,175
Latest member
Sheenamarie ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back