# Lookup to determine if value falls between two sets of coordinates

#### Xipha

##### New Member
I'm working on a problem where I have to assign a multiplier to a data entry based on the geographic region in which it is located. Each region has been defined as a square with two corner coordinates (see data below), and I need to return the region number that a third set of coordinates falls within. For the one set of regions I have it figured out using nested if statements as there are only 7 regions, however there is a second region map with dozens of regions and I would like to be able to perform the same exercise for that map without having to use dozens of nested IF statements. Is there a better way?

For example, if I had coordinates TWP = 40, RG = 18, Meridian =4, then it would return Area =2 (since it is between the coordinates in row 3).

 TWP Rg Meridian Area 1 50 1 16 4 1 1 59 16 30 4 2 1 59 1 7 5 2 1 59 7 30 5 3 1 59 1 13 6 3 59 92 21 30 5 3 59 92 1 13 6 3

<tbody>
</tbody>

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Hi,

You can use an Index/Match combination in an array formula to do it:

Excel 2013
ABCDEFGHIJ
1TWPRgMeridianArea40184
215011641Area =2
3
4
5159163042
61591752
7
815973053
915911363
105992213053
11599211363
Sheet1
Cell Formulas
RangeFormula
I2{=INDEX(F2:F11,MATCH(1,(A2:A11<=H1)*(B2:B11>=H1)*(C2:C11<=I1)*(D2:D11>=I1)*(E2:E11=J1),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Last edited:
Thanks, I have avoided array formulas in the past due to working with very large data sets ~10-20,000 rows, but I will see if it works to calculate then I can just convert to a value so it doesn't recalculate when I open/save etc.

Replies
1
Views
104
Replies
1
Views
88
Replies
2
Views
175
Replies
1
Views
219
Replies
42
Views
943

1,203,094
Messages
6,053,504
Members
444,667
Latest member
KWR21

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back