Need ROW number based on a MIN array formula.

Need to figure out how to get the following:

I have a large set of LAT-A and Long-A coords and a separate file of LAT-B and LONG-B coords (50,000 rows or more each)
I have been able to find the distance to the CLOSEST point in the LAT-A/LONG-A points to a single LAT-B and LONG-B. But I can NOT figure out how to tell me the ROW in the LAT-A and Long-A worksheet was the CLOSEST.

Sheet with LAT-A and LONG-A
LAT-A LONG-A
1 23.81142998 91.26706696
2 26.22337 78.173622
3 23.81106758 91.26634979
4 23.81109238 91.26612854
etc

Sheet with LAT-B and LONG-B
LAT-B LONG-B
1 19.18746948 72.8460
2 26.20013618 78.1618
3 26.19869995 78.1663
4 23.81142998 91.2671
etc

Here is my formula to determine the closest LAT-A/LONG-A point to LAT-B / LONG-B
{=MIN(0.621371*(3958.818*((2*ASIN(SQRT((SIN((RADIANS(LATB)-RADIANS($LATA$2:LATA$50000))/2)^2)+COS(RADIANS(LATB))*COS(RADIANS(LATA$50000))*(SIN((RADIANS(LONGB)-RADIANS(LONGA$2:LONGA$50000))/2)^2)))))))}

It works great.

What I am trying to do is figure out WHICH ROW from the LAT-A/LONG-A tab provided the shortest distance!

So basically, a long formula determines the shortest distance between one point and thousands of others...but I can't figure out how to know WHICH of those thousands gave me the closest point.

Any help would be much appreciated.

I don't really understand what you need but this is how I would approach this.

use

Please Login or Register  to view this content.

to find the first row that is larger than the value you are looking for.

calculate whether this row is closer to your value or the previous row.

present the closest value as your answer.

You should have posted an example.

Let me try to explain a difference way...

I have attacehd a file.
Basically trying to find the row number but way more complicated than ROW() or match() (I think). Maybe I am missing something.

Hopefully my new post helps answer your question... Sorry for not including the file before.

Absolutely awesome solution. Thanks!