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
Solved

Excel vlookup for lat/long points against ranges

Posted on 2010-11-29
11
505 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

791 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