VLOOKUP in 2 fields of numbers

[Richolds]

I have been looking through a lot of very similar threads on this particular subject but have thus far been unable to find a solution that works for me. I imagine that there is something on these forums that would work but I figured it couldn't really hurt to try posting my own thread.

I have 2 separate tables of data, one of them is static and is being used for a reference. Specifically these are fixed locations along a road for utility poles.The second table is data that is being collected for events that happen along a road.

I want to use the functionality of VLOOKUP because it will look for values close to the one being searched for and I won't necessarily get exact hits on the gps location. I would like my spreadsheet to display the closest pole to the location at which the event occurs.

For reference I am using Excel 2003 and I apologize in advance for my data format but I don't have administrator rights at work to install excel jeanie.

Event Occurences

Hit 40.2356 -62.3231 #CLOSESTPOLE
Hit 40.1231 -62.3311 #CLOSESTPOLE
Hit 40.2122 -61.895 #CLOSESTPOLE
Hit 40.2356 -61.532 #CLOSESTPOLE

Pole Locations
P121 40.2222 -62.3221
P122 40.1334 -61.7214
P123 40.2432 -62.4312

Obviously the actual tables are much larger than that but I am looking for the function that will take the locations of the occurence and replace #CLOSESTPOLE with the P12X number from the second table.

Sorry if this is at all unclear but any help that you can offer would be greatly appreciated!

 

[T. Valko]

I have been looking through a lot of very similar threads on this particular subject but have thus far been unable to find a solution that works for me. I imagine that there is something on these forums that would work but I figured it couldn't really hurt to try posting my own thread.

I have 2 separate tables of data, one of them is static and is being used for a reference. Specifically these are fixed locations along a road for utility poles.The second table is data that is being collected for events that happen along a road.

I want to use the functionality of VLOOKUP because it will look for values close to the one being searched for and I won't necessarily get exact hits on the gps location. I would like my spreadsheet to display the closest pole to the location at which the event occurs.

For reference I am using Excel 2003 and I apologize in advance for my data format but I don't have administrator rights at work to install excel jeanie.

Event Occurences

Hit 40.2356 -62.3231 #CLOSESTPOLE
Hit 40.1231 -62.3311 #CLOSESTPOLE
Hit 40.2122 -61.895 #CLOSESTPOLE
Hit 40.2356 -61.532 #CLOSESTPOLE

Pole Locations
P121 40.2222 -62.3221
P122 40.1334 -61.7214
P123 40.2432 -62.4312

Obviously the actual tables are much larger than that but I am looking for the function that will take the locations of the occurence and replace #CLOSESTPOLE with the P12X number from the second table.

Sorry if this is at all unclear but any help that you can offer would be greatly appreciated!

Is this all in one cell: 40.2356 -62.3231

 

[Richolds]

No separate cells. I could make it one cell if you think that would be the right way to go, but I was under the impression that if I treated the numbers as text then I wouldn't be able to look for numbers close to the one I'm referencing.

 

[T. Valko]

No separate cells. I could make it one cell if you think that would be the right way to go, but I was under the impression that if I treated the numbers as text then I wouldn't be able to look for numbers close to the one I'm referencing.

Ok, if those numbers are in 2 cells then which one are you wanting to lookup?

Book1
	A	B	C
2	Hit	40.2356	-62.3231
3	Hit	40.1231	-62.3311
4	Hit	40.2122	-61.895
5	Hit	40.2356	-61.532
6	_	_	_
7	_	_	_
8	_	_	_
9	_	_	_
10	P121	40.2222	-62.3221
11	P122	40.1334	-61.7214
12	P123	40.2432	-62.4312
Sheet1

 

[Richolds]

I want to be able to check both, one is a lattitude coordinate and one is a longitude coordinate. I want to be able to find the closest pole to the GPS coordinates that the event returns.

That's the problem I have, if all the events occured at the same of either one I would be all set, unfortunately those **** engineers couldn't even draw a straight line.

Thanks for the quick responses by the way I really appreciate it.

 

[Richolds]

With reference to the table you posted by the way, the field I would be trying to populate would be column D with the pole number taken from the lower section of column A.

So for example in D2 I would want P123 to appear to the user.

 

[T. Valko]

With reference to the table you posted by the way, the field I would be trying to populate would be column D with the pole number taken from the lower section of column A.

So for example in D2 I would want P123 to appear to the user.

Hmmm...

I'm not sure how to do this when considering BOTH numbers. I'm sure there is some way to do it but I'm afraid I don't know how to do it!

I'll keep an eye on this thread so that if someone posts a solution I'll be able to learn something.

Good luck!

 

[Richolds]

Thank you.

I've been thinking about it and I know you can convert 2 points of lattitude and longitude into a distance between them, but I'm not sure if that would be useful or not, given that the MIN function can only look at 30 values (if I'm reading the help file correctly). Even if I were able to make use of this situation I imagine it would be ludicrously inefficient as far as finding the right pole when I enter new data points.

 

[T. Valko]

Thank you.

I've been thinking about it and I know you can convert 2 points of lattitude and longitude into a distance between them, but I'm not sure if that would be useful or not, given that the MIN function can only look at 30 values (if I'm reading the help file correctly). Even if I were able to make use of this situation I imagine it would be ludicrously inefficient as far as finding the right pole when I enter new data points.

I'm thinking there must be some trig function that can do this but I'm not familar with the method.

 

[Richolds]

Taken from http://www.movable-type.co.uk/scripts/latlong.html . I'm trying to learn a little bit more about the min function to see if that would be usable or not.<TABLE><TBODY><TR><TD>Excel:</TD><TD class=code>
=ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(lon2-lon1))*6371
</TD></TR></TBODY></TABLE>