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.  
sbauchAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
barry houdiniConnect With a Mentor Commented:
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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.