Help with a Latitude Longitude lookup required!

[plotboxmark]

Hi

I was hoping someone could give me some assistance with a query I have to complete for a mapping project. I have calculated out the Grid intersections of a theoretical grid compared to true Latitude and Longitude co-ordinates:

Grid	LAT	LONG
A1	37.8769569	-122.5262497
A2	37.87692395	-122.5262488
A3	37.876891	-122.5262479
A4	37.87685806	-122.526247
A5	37.87682511	-122.5262461
A6	37.87679216	-122.5262452
A7	37.87675921	-122.5262443
A8	37.87672626	-122.5262434
A9	37.87669332	-122.5262425
A10	37.87666037	-122.5262416
A11	37.87662742	-122.5262407

<tbody>
</tbody>

The intersection is via the Top Left hand corner of the Grid square, and Alphas run Left to Right from A to CS, and Numerics run Up to Down from 1 to 180.

I then have a table of specific points known by Lat/Long that I need to reference to the Grid that the point lands in. The table of known points is around 7000 points long.

Is there a way I could lookup the Latitude value (which may throw back multiple rows with a value that matches between its starting value and the next cell starting value), and at the same time lookup the Longitude values for the same, but only return a result that matches both?

Its confusing me, hence the slightly awkwardly posed question, but any help would be great

Thanks

 

[DRSteele]

If I understand correctly, you wish to look up the LAT and LONG figures and if there is an exact match in the giant list then report the appropriate grid.

I think this might un-furrow your brow:

	A	B	C	D	E	F	G
1	Grid	LAT	LONG		query (LAT and LONG)		Grid
2	A1	37.8769569	-122.5262497		37.876891	-122.5262479	A3
3	A2	37.87692395	-122.5262488
4	A3	37.876891	-122.5262479
5	A4	37.87685806	-122.526247
6	A5	37.87682511	-122.5262461
7	A6	37.87679216	-122.5262452
8	A7	37.87675921	-122.5262443
9	A8	37.87672626	-122.5262434
10	A9	37.87669332	-122.5262425
11	A10	37.87666037	-122.5262416
12	A11	37.87662742	-122.5262407

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet19​

Array Formulas Cell Formula G2 {=IFERROR(INDEX(A2:A12,MATCH(E2&F2,B2:B12&C2:C12,0)),"No Match")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[plotboxmark]

If I understand correctly, you wish to look up the LAT and LONG figures and if there is an exact match in the giant list then report the appropriate grid.

I think this might un-furrow your brow:

	A	B	C	D	E	F	G
1	Grid	LAT	LONG		query (LAT and LONG)		Grid
2	A1	37.8769569	-122.5262497		37.876891	-122.5262479	A3
3	A2	37.87692395	-122.5262488
4	A3	37.876891	-122.5262479
5	A4	37.87685806	-122.526247
6	A5	37.87682511	-122.5262461
7	A6	37.87679216	-122.5262452
8	A7	37.87675921	-122.5262443
9	A8	37.87672626	-122.5262434
10	A9	37.87669332	-122.5262425
11	A10	37.87666037	-122.5262416
12	A11	37.87662742	-122.5262407

<tbody>
</tbody>

Sheet19​

Array Formulas Cell Formula G2 {=IFERROR(INDEX(A2:A12,MATCH(E2&F2,B2:B12&C2:C12,0)),"No Match")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thanks for your assistance, and that looks like its almost cracked it, but there is an added layer of complexity, as the Latitude and longitudes are not exact matches in the table as compared with the points we are looking up. So I know that the Latitude for instance could be in say 20 separate grid locations, and the longitude could be in 20 different grid locations, but only 1 grid reference will be in both lists of matched grid references.

Can you think of an additional layer of referencing formula that could assist?

So far, you have helped greatly and I really appreciate your time and help!

 

[DRSteele]

Now my brow in the one furrowed. Hold please.

 

[plotboxmark]

Ha ha!

I appreciate your help