# 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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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

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.

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

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:
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
4
Views
476
Replies
8
Views
341
Replies
5
Views
381
Replies
3
Views
538
Replies
1
Views
107

Threads
1,219,905
Messages
6,150,907
Members
450,991
Latest member
ExcelDoer

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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