Lookup to determine if value falls between two sets of coordinates

[Xipha]

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>

 

[RickXL]

Hi,

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

Excel 2013
	A	B	C	D	E	F	G	H	I	J
1	TWP		Rg		Meridian	Area		40	18	4
2	1	50	1	16	4	1		Area =	2
3
4
5	1	59	16	30	4	2
6	1	59	1	7	5	2
7
8	1	59	7	30	5	3
9	1	59	1	13	6	3
10	59	92	21	30	5	3
11	59	92	1	13	6	3
Sheet1

Cell Formulas
Range	Formula
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.

 

[Xipha]

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.