Solved

Excel vlookup for lat/long points against ranges

Posted on 2010-11-29
11
515 Views
Last Modified: 2012-06-21
I have 2 spreadsheets, one with a list of addresses with latitude longitude points, and the other is an index of neighborhoods with minimum and maximum values for longitude and latitude.  (They're essentially best-fit bounding boxes for non-square neighborhoods, so they overlap a bit).  I want to determine which neighborhood each of my addresses are in.  Vlookup seems the way to do it, but I cant seem to figure it out.  
0
Comment
Question by:sbauch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 34233708
Can you post a simple example? How are the values shown for your addresses and in the min/max data?

regards, barry
0
 
LVL 3

Expert Comment

by:babesia
ID: 34233733
VBA coding is one to do it.

Can you give me some example data.
0
 

Author Comment

by:sbauch
ID: 34233944
Here are samples of the 2 sheets.  Not sure if any will work, meaning I dont know if any of the provided addresses will be in the example neighborhoods
addressesgeo.csv
Neighborhood-Bounds.csv
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:CapnCrnch
ID: 34233986
Hi sbauch,

The number thing to remember is that the list that you are searching against needs to:

a.  Have the first column be in alphabetical order
b.  The first column must be the column in which you are searching against.

Near as I can tell you are needing to look at two different values.  The addresses with Longitude and Latitude values (assuming these are in 2 different cells).  And then the index neighborhoods with min and max latitude and longitude values (again, assuming 2 different cells).

You can do a multiple conditions IF statement combine with a VLOOKUP.

If For example you have the following data:

In A2 through A8 you have numbers 9-15 (could be your longitude numbers).  
In B2 through B8 you have numbers 1-7 (could be your latitude numbers).
In D2 through D8 you have your list of neighborhoods.
In E2 through E8 you have numbers 9-15 (could be your max longitude numbers).  
In F2 through F8 you have numbers 1-7 (could be your min longitude numbers).

Use a formula like this:
=IF(AND(VLOOKUP(E2,$B$2:$B$8,1,FALSE),($B$2 >=E2),($B$2<=F2)), D2,"NA")

This only accounts for the mix/max of Longitude, but you can add on additional conditions to do the comparison.

I think this would work.

Good luck.

capn
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 34234196
Try this formula in addressesgeo F2 copied down (assuming Neighborhood bounds is open)

=INDEX('Neighborhood-Bounds.csv'!$G$2:$G$14,MATCH(1,INDEX((D2>='Neighborhood-Bounds.csv'!$D$2:$D$14)*(D2<='Neighborhood-Bounds.csv'!$E$2:$E$14)*(E2>='Neighborhood-Bounds.csv'!$B$2:$B$14)*(E2<'Neighborhood-Bounds.csv'!$C$2:$C$14),0),0))

It'll still work if you close the neighborhood bounds workbook but the formula will adjust to show the whole path.......

I get matches in rows 7,8,12,13 and 16, does that look right?

you could get rid of #N/A values if you want, what version of excel are you using?

regards, barry
0
 

Author Comment

by:sbauch
ID: 34234330
Using 2008 for Mac.  That formula works for me, but I do see one mistake, and there could be more.  Address 8 finds itself in the neighborhood of East Harlem but my data tells me it is in the Bronx (East Harlem is in Manhattan).  Im guessing this must be because of the overlapping squares.  Is there a way to highlight which addresses found themselves in 2 ranges and thus privileged one?  Or maybe also use the Borough field (which is in both sheets) to help control this problem (although it wouldn't be that helpful as it would only help with the addresses that are on borough borders rather than interior neighborhoods)?
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34234466
This formula copied down will give the number of matches

=SUMPRODUCT((D2>='Neighborhood-Bounds.csv'!$D$2:$D$14)*(D2<='Neighborhood-Bounds.csv'!$E$2:$E$14)*(E2>='Neighborhood-Bounds.csv'!$B$2:$B$14)*(E2<'Neighborhood-Bounds.csv'!$C$2:$C$14))

I notice that only row 8 has a 2

The previous formula I suggested will always give the first match (working down the list). This version will give the last match

=SUMPRODUCT((D2>='Neighborhood-Bounds.csv'!$D$2:$D$14)*(D2<='Neighborhood-Bounds.csv'!$E$2:$E$14)*(E2>='Neighborhood-Bounds.csv'!$B$2:$B$14)*(E2<'Neighborhood-Bounds.csv'!$C$2:$C$14))

....so if there are never more than 2 matches for any row then using that one and the first one will give both possible neighborhoods

regards, barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34234483
Sorry, I posted the same formula twice there, the second one should use LOOKUP to get the last match.......i.e.

=LOOKUP(2,1/(D2>='Neighborhood-Bounds.csv'!$D$2:$D$14)/(D2<='Neighborhood-Bounds.csv'!$E$2:$E$14)/(E2>='Neighborhood-Bounds.csv'!$B$2:$B$14)/(E2<'Neighborhood-Bounds.csv'!$C$2:$C$14),'Neighborhood-Bounds.csv'!$G$2:$G$14)

regards, barry

0
 

Author Comment

by:sbauch
ID: 34234661
Okay awesome.  Some of my addresses are in as many as 4 neighborhoods however.  The neighborhood names are going to be implemented as taxonomy terms on a Drupal site, so I can tag addresses with multiple neighborhoods.  How about a formula for listing all neighborhoods?  Or separate formulas for listing the second and third neighborhoods on the addresses with >2 matches?  
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 34234835
Ok, if you use the SUMPRODUCT formula in F2 copied down to get the number of matches for each row then you can use this "array formula in G2

=IF($F2<COLUMNS($G2:G2),"",INDEX('Neighborhood-Bounds.csv'!$G$2:$G$14,SMALL(IF(($D2>='Neighborhood-Bounds.csv'!$D$2:$D$14)*($D2<='Neighborhood-Bounds.csv'!$E$2:$E$14)*($E2>='Neighborhood-Bounds.csv'!$B$2:$B$14)*($E2<'Neighborhood-Bounds.csv'!$C$2:$C$14),ROW('Neighborhood-Bounds.csv'!$D$2:$D$14)-ROW('Neighborhood-Bounds.csv'!$D$2)+1),COLUMNS($G2:G2))))

confirmed with CTRL+SHIFT+ENTER and copied across four columns to J2 and then down the columns

To confirm with CTRL+SHIFT+ENTER put the formula in G2 then press F2 key and hold down CTRL and SHIFT  while pressing ENTER, curly braces like { and } should appear around the formula in the formula bar. You need to do that before you copy the formula across and down.

That formula will leave G2:J2 blank if F2 =0 but if F2 =1 G2 will be filled in with the first match, if F2 =2 then G2 and H2 will be filled in with the first two matches, if F2 =3.......etc.

regards, barry
0
 

Author Comment

by:sbauch
ID: 34234979
Works like a charm.  Much thanks Barry
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question