Solved

Excel vlookup for lat/long points against ranges

Posted on 2010-11-29
11
466 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
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
 

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now