Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 571
  • Last Modified:

Excel vlookup for lat/long points against ranges

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
sbauch
Asked:
sbauch
1 Solution
 
barry houdiniCommented:
Can you post a simple example? How are the values shown for your addresses and in the min/max data?

regards, barry
0
 
babesiaCommented:
VBA coding is one to do it.

Can you give me some example data.
0
 
sbauchAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
CapnCrnchCommented:
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
 
barry houdiniCommented:
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
 
sbauchAuthor Commented:
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
 
barry houdiniCommented:
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
 
barry houdiniCommented:
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
 
sbauchAuthor Commented:
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
 
barry houdiniCommented:
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
 
sbauchAuthor Commented:
Works like a charm.  Much thanks Barry
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now