# Need ROW number based on a MIN array formula.

1. ## 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

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.  Register To Reply

2. ## Re: Need ROW number based on a MIN array formula.

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.

You should have posted an example.  Register To Reply

3. ## Re: Need ROW number based on a MIN array formula.

You are combinig data from two columns to get the MIN.
For which value do you need the ROW number to be returned?  Register To Reply

4. ## Re: Need ROW number based on a MIN array formula.

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.  Register To Reply

5. ## Re: Need ROW number based on a MIN array formula.

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

6. ## Re: Need ROW number based on a MIN array formula.

=MATCH(MIN(ABS(\$A\$2:\$A\$5-\$D2)+ABS(\$B\$2:\$B\$5-\$E2)),ABS(\$A\$2:\$A\$5-\$D2)+ABS(\$B\$2:\$B\$5-\$E2),0)
Confirm Control+shift+Enter  Register To Reply

7. ## Re: Need ROW number based on a MIN array formula.

Absolutely awesome solution. Thanks!  Register To Reply

8. ## Re: Need ROW number based on a MIN array formula.

You are welcome.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 