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")


[TABLE="width: 668"]
<colgroup><col span="3"><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Location[/TD]
[TD][/TD]
[TD]Location[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1.1[/TD]
[TD]1.2[/TD]
[TD]Point 1 >> Point 2[/TD]
[TD][/TD]
[TD]Point 1 >> Point 2[/TD]
[TD]0[/TD]
[TD]2.3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]6.5[/TD]
[TD]6.8[/TD]
[TD]Point 3 >> Point 4[/TD]
[TD][/TD]
[TD]Point 2 >> Point 3[/TD]
[TD]2.3[/TD]
[TD]6.1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2.35[/TD]
[TD]2.6[/TD]
[TD]Point 2 >> Point 3[/TD]
[TD][/TD]
[TD]Point 3 >> Point 4[/TD]
[TD]6.1[/TD]
[TD]7.8[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]9.5[/TD]
[TD]9.8[/TD]
[TD]Point 5 >> Point 6[/TD]
[TD][/TD]
[TD]Point 4 >> Point 5[/TD]
[TD]7.8[/TD]
[TD]9.2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3.6[/TD]
[TD]4.6[/TD]
[TD]Point 2 >> Point 3[/TD]
[TD][/TD]
[TD]Point 5 >> Point 6[/TD]
[TD]9.2[/TD]
[TD]10.1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]7.6[/TD]
[TD]8.3[/TD]
[TD]No suitable range[/TD]
[TD][/TD]
[TD]Point 6 >> Point 7[/TD]
[TD]10.1[/TD]
[TD]13.2[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.