Be seen. Boost your questionâ€™s priority for more expert views and faster solutions

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.

regards, barry

addressesgeo.csv

Neighborhood-Bounds.csv

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

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

=SUMPRODUCT((D2>='Neighbor

I notice that only row 8 has a 2

The previous formula I suggested will always give the

=SUMPRODUCT((D2>='Neighbor

....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

=LOOKUP(2,1/(D2>='Neighbor

regards, barry

=IF($F2<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

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.

=INDEX('Neighborhood-Bound

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