Alternative to nested IF function

[jjcrazy]

I have a list of locations, with their coordinates, and I have a set of regions of which I have the coordinates of the border points. Now I would like excel to specify for me in what region every location is.

I was thinking of using a nested IF function, but I have up to 20 regions so I am hoping there may be an easier way to do this.

For example:

[TABLE="width: 607"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]location[/TD]
[TD]N[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]location 1[/TD]
[TD="align: right"]51,2088[/TD]
[TD="align: right"]5,995877[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]name[/TD]
[TD]top[/TD]
[TD]left[/TD]
[TD]bottom[/TD]
[TD]right[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]region 1[/TD]
[TD="align: right"]51,068352[/TD]
[TD="align: right"]5,661775[/TD]
[TD="align: right"]50,745750[/TD]
[TD="align: right"]6,054801[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]region 2[/TD]
[TD="align: right"]51,561538[/TD]
[TD="align: right"]5,823070[/TD]
[TD="align: right"]51,068352[/TD]
[TD="align: right"]5,661775[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]region 3[/TD]
[TD="align: right"]51,561538[/TD]
[TD="align: right"]5,201854[/TD]
[TD="align: right"]51,068352[/TD]
[TD="align: right"]5,823070[/TD]
[/TR]
</tbody>[/TABLE]


So if C2 falls between C5 and E5, AND D2 falls between D5 and F5, I want it to tell me region 1.
Else if C2 falls between C6 and E6, AND D2 falls between D6 and F6, I want it to tell me region 2, etc.

Is there any function that can do this more easily?

 

[AlanY]

try this

copy G5 down to all regions

Excel 2012
	A	B	C	D	E	F	G
1		location	N	E
2		location 1	51.208800	5.995877		region 4
3
4		name	top	left	bottom	right
5		region 1	51.068352	5.661775	50.745750	6.054801	FALSE
6		region 2	51.561538	5.823070	51.068352	5.661775	FALSE
7		region 3	51.561538	5.201854	51.068352	5.823070	FALSE
8		region 4	51.361538	5.201854	51.068352	6.823070	TRUE
Sheet1

Cell Formulas
Range	Formula
F2	=INDEX(B:B,MATCH(TRUE,G:G,0))
G5	=AND($C$2>=MIN(C5,E5),$C$2<=MAX(C5,E5),$D$2>=MIN(D5,F5),$D$2<=MAX(D5,F5))

 

[jjcrazy]

Thanks so much!
The only thing is, I have over a thousand locations that I want to do this for. This will only work for one at a time right?

 

[AlanY]

yes, this solution is not good for a long list of co-ordinates, let me have another think about it

 

[AlphaFrog]

Try this array formula.
Confirm with Ctrl+Shift+Enter

=INDEX(B5:B8,MATCH(1,(C2<=$C$5:$C$8)*(C2>=$E$5:$E$8)*(D2>=$D$5:$D$8)*(D2<=$F$5:$F$8),0))

 

[AlanY]

Try this array formula.
Confirm with Ctrl+Shift+Enter

=INDEX(B5:B8,MATCH(1,(C2<=$C$5:$C$8)*(C2>=$E$5:$E$8)*(D2>=$D$5:$D$8)*(D2<=$F$5:$F$8),0))

problem when the co-ords are the other way round, e.g.

name top left bottom right
region 1 51.068352 5.661775 50.74575 6.054801
region 2 51.561538 5.82307 51.068352 5.661775
region 3 51.561538 5.201854 51.068352 5.82307
region 4 51.068352 5.201854 51.361538 6.82307

location 1 51.208800 5.995877 should still in region 4.

 

[AlphaFrog]

You cant list the coordinates in the region table consistently so that one side is always larger than its oppisite?

 

[jjcrazy]

You cant list the coordinates in the region table consistently so that one side is always larger than its oppisite?

Yes I guess I can make the regions in a way that they don't overlap. If they are exclusive than this must work!

Thanks!

 

[jjcrazy]

Try this array formula.
Confirm with Ctrl+Shift+Enter

=INDEX(B5:B8,MATCH(1,(C2<=$C$5:$C$8)*(C2>=$E$5:$E$8)*(D2>=$D$5:$D$8)*(D2<=$F$5:$F$8),0))

Ok so I tried it with this table and the values below:

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl68, width: 64"]Region[/TD]
[TD="class: xl67, width: 64"]bottom[/TD]
[TD="class: xl67, width: 64"]top[/TD]
[TD="class: xl67, width: 64"]left[/TD]
[TD="class: xl67, width: 64"]right[/TD]
[/TR]
[TR]
[TD="class: xl68"]region 1[/TD]
[TD="class: xl67, align: right"]51[/TD]
[TD="class: xl67, align: right"]51,4[/TD]
[TD="class: xl67, align: right"]3,4[/TD]
[TD="class: xl67, align: right"]3,67[/TD]
[/TR]
[TR]
[TD="class: xl67"]region 2[/TD]
[TD="class: xl67, align: right"]51[/TD]
[TD="class: xl67, align: right"]51,34[/TD]
[TD="class: xl67, align: right"]3,67[/TD]
[TD="class: xl67, align: right"]3,9[/TD]
[/TR]
[TR]
[TD="class: xl67"]region 3[/TD]
[TD="class: xl67, align: right"]51[/TD]
[TD="class: xl67, align: right"]51,35[/TD]
[TD="class: xl67, align: right"]3,9[/TD]
[TD="class: xl67, align: right"]4,36[/TD]
[/TR]
[TR]
[TD="class: xl67"]region 4[/TD]
[TD="class: xl67, align: right"]51,35[/TD]
[TD="class: xl67, align: right"]51,7[/TD]
[TD="class: xl67, align: right"]4,1[/TD]
[TD="class: xl67, align: right"]4,35[/TD]
[/TR]
[TR]
[TD="class: xl67"]region 5[/TD]
[TD="class: xl67, align: right"]51,35[/TD]
[TD="class: xl67, align: right"]51,7[/TD]
[TD="class: xl67, align: right"]3,8[/TD]
[TD="class: xl67, align: right"]4,1[/TD]
[/TR]
[TR]
[TD="class: xl67"]region 6[/TD]
[TD="class: xl67, align: right"]51,35[/TD]
[TD="class: xl67, align: right"]51,7[/TD]
[TD="class: xl67, align: right"]3,4[/TD]
[TD="class: xl67, align: right"]3,8[/TD]
[/TR]
[TR]
[TD="class: xl67"]region 7[/TD]
[TD="class: xl67, align: right"]51,7[/TD]
[TD="class: xl67, align: right"]51,8[/TD]
[TD="class: xl67, align: right"]3,4[/TD]
[TD="class: xl67, align: right"]3,8[/TD]
[/TR]
[TR]
[TD="class: xl67"]region 8[/TD]
[TD="class: xl67, align: right"]51,35[/TD]
[TD="class: xl67, align: right"]51,71[/TD]
[TD="class: xl67, align: right"]4,35[/TD]
[TD="class: xl67, align: right"]4,65[/TD]
[/TR]
[TR]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl67"][/TD]
[/TR]
[TR]
[TD="class: xl67"]loc a[/TD]
[TD="class: xl69, align: right"]51,32698[/TD]
[TD="class: xl69, align: right"]3,489644[/TD]
[TD="class: xl69, align: center"]#VALUE![/TD]
[TD="class: xl67"][/TD]
[/TR]
</tbody>[/TABLE]

And it returned #value..

I typed the following formula:
=INDEX($A$1:$A$9;MATCH(1;(B12<=$C$1:$C$9)*(B12>=$B$1:$B$9)*(C12>=$D$1:$D$9)*(C12<=$E$1:$E$9);0))

What did I do wrong?

 

[jjcrazy]

never mind, i forgot the ctrl+shift+enter part again! haha - it works now, thanks!