Challenging IF task with proximity

[adamxcl]

I've been working on this for over a day straight now, including hours of reading threads on here and can't figure out if it's even possible. Hopefully someone has a thought on it here. I'm trying to look in another file at two sets of numbers that are unique in thousands of rows. If both of the numbers are within set of min and max numbers, it is to fill in the field or leave blank.

Manual one on one it works correctly.

=IF(AND('RestFile.xlsx'!A49<=N3,'RestFile.xlsx'!A49>=O3,'RestFile.xlsx'!B49>=L3,'RestFile.xlsx'!B49<=M3),"RestFile","")


Trying to make it work to look at a big range does not.

=IF(AND('RestFile.xlsx'!$A$1:$A$13910<=N7,'RestFile.xlsx'!$A$1:$A$13910>=O7,'RestFile.xlsx'!$B$1:$B$13910>=L7,'RestFile.xlsx'!$B$1:$B$13910<=M7,I7<>"None"),"RestLoc","")

I thought for a bit I had it working. The receiving file that I'm pulling data into has about 20,000 records. The second file with the reference data that I'm looking to flag/note has 13,910 lines. As soon as the receiving file row goes past the 13,910 mark, it gives a VALUE error. Which means it's connecting to the line numbers for some reason when it shouldn't.

Overall view if wanted: I'm working with latitude longitude coordinates and I want to mark those within a certain proximity. Because lat and lng are two different numbers, it's a challenge. I'm doing all this to find the added data that is within a certain distance of the points that I have. So I created min and max coordinates columns for each legitimate point. One set for lat and one for lng. Then I am looking to flag the added points that fall within the min max/distance range. Hope that makes sense.

Thanks! Adam

 

[adamxcl]

I didn't think it was possible but I wanted to make sure. I thought there must be someway to look up records that fell between two ranges.

 

[Begbie]

You could use the sumproduct function, VERY useful tool, might take a while to run it over so many records, but it works!