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:

A	B	C	D	E	F
1	location	N	E
2	location 1	51,2088	5,995877
3
4	name	top	left	bottom	right
5	region 1	51,068352	5,661775	50,745750	6,054801
6	region 2	51,561538	5,823070	51,068352	5,661775
7	region 3	51,561538	5,201854	51,068352	5,823070

<colgroup><col><col><col span="4"></colgroup><tbody>
</tbody>

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:

Region	bottom	top	left	right
region 1	51	51,4	3,4	3,67
region 2	51	51,34	3,67	3,9
region 3	51	51,35	3,9	4,36
region 4	51,35	51,7	4,1	4,35
region 5	51,35	51,7	3,8	4,1
region 6	51,35	51,7	3,4	3,8
region 7	51,7	51,8	3,4	3,8
region 8	51,35	51,71	4,35	4,65
loc a	51,32698	3,489644	#VALUE!

<tbody>
</tbody>

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!