How to get a value in a square matrix with two conditions

Hi guys,

I have a square matrix that represents the distances between over 1500 places.
The first column is a transpose of the first row. So it's possible to see every distance between the different places.
What I'm trying is: when I choose one place, I need to find the closest one. As it's a square matrix, there is a diagonal with 0s, which represents the distance between the same place.

I tried this formula

{=INDEX(A2:A6,MATCH(MIN(IF(B1:F1=H4,IF(B2:F6>0,B2:F6))),0))}

, where H2 is the origin, A2:A6 is the destiny. But it's not working.

Does someone have another idea for this puzzle?

I attached an example.

Kind Regards,
Alex

Hi -

If you put this formula into cell I4, it should give you the closest destination to the input location.

=INDEX(B1:F1,1,MATCH(H5,OFFSET($A$1,MATCH(H4,A:A,0)-1,1,1,5),0))

This just uses the OFFSET function to crate an array of 5 values based on the row found by the inner MATCH function, and the column found by the outer MATCH function. Then INDEX just uses those columns to find the correct destination in the top row.

I hard coded in the 5 columns in the OFFSET function. If you have a set number of columns, you can either swap that number into hte formula, or you could use the COUNT function nested in there to determine how wide the array should be.

Hope this helps.