Lookup & Compare Range of Values

[Steve 1962]

Book1
	A	B	C	D	E	F	G
1	Start	End	Location		Location	Start	End
2	1.10	1.20	Point 1 >> Point 2		Point 1 >> Point 2	0.00	2.30
3	6.50	6.80	Point 3 >> Point 4		Point 2 >> Point 3	2.30	6.10
4	2.35	2.60	Point 2 >> Point 3		Point 3 >> Point 4	6.10	7.80
5	9.50	9.80	Point 5 >> Point 6		Point 4 >> Point 5	7.80	9.20
6	3.60	4.60	Point 2 >> Point 3		Point 5 >> Point 6	9.20	10.10
7	7.60	8.30	Point 4 >> Point 5		Point 6 >> Point 7	10.10	13.20
Sheet1

Hi

Looking to have a formula (in column C) that looks at the range of distances in columns A & B and compares them against the right-hand table (cells E1:G7) to give the output in column C that identifies the correct range label in column E.

Thanks

 

[admiral100]

Hi,

Try this : Ctrl+Shift+Enter NOT just enter

C2 =IFERROR(INDEX($E$2:$E$7,MATCH(1,IF(B2<$G$2:$G$7,IF(A2>$F$2:$F$7,1)))),"No suitable range")

	A	B	C	D	E	F	G
1	Start	End	Location		Location	Start	End
2	1.1	1.2	Point 1 >> Point 2		Point 1 >> Point 2	0	2.3
3	6.5	6.8	Point 3 >> Point 4		Point 2 >> Point 3	2.3	6.1
4	2.35	2.6	Point 2 >> Point 3		Point 3 >> Point 4	6.1	7.8
5	9.5	9.8	Point 5 >> Point 6		Point 4 >> Point 5	7.8	9.2
6	3.6	4.6	Point 2 >> Point 3		Point 5 >> Point 6	9.2	10.1
7	7.6	8.3	No suitable range		Point 6 >> Point 7	10.1	13.2

<colgroup><col span="3"><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[Steve 1962]

Hey thanks very much.

It ceases to amaze me how many brilliant people are out there and you are one "admiral100". Works fine.

Have a good week.