Distance Range - Vlookup ???

[Steve 1962]

Hi

Just wondering how I can obtain an output which is based on a range of distances located in two adjacent columns ?

EG:

Increasing distances as we go down the page -

Cell A1 = 5.0 & cell B1 = 5.5 / Need an output to show that these values lay between location "A" and "B"
Cell A2 = 6.2 & cell B2 = 7.8 / Need an output to show that these values lay between location "C" and "D"
Cell A3 = 9.5 & cell B3 = 9.8 / Need an output to show that these values lay between location "E" and "F"

...... and so on.

I'm assuming that the answer lays somewhere in a VLOOKUP ?

Thanks

Steve

 

[Weazel]

so, because your ranges aren't consistent, its a little more complicated.

What would you need returned if A1 contained 5.7 or A2 contained 8.5 ?

 

[Steve 1962]

so, because your ranges aren't consistent, its a little more complicated.

What would you need returned if A1 contained 5.7 or A2 contained 8.5 ?

Sorry forgot to mention, for the example -

Location "A" to "B" would be "A" = 5.0 and "B" would be = 6.0 (Range 1).
Location "C" to "D" would be "C" = 6.1 and "D" would be = 8.0 (Range 2).
Location "E" to "F" would be "E" = 8.1 and "F" would be = 10.0 (Range 3).

 

[markmzz]

Hi

Just wondering how I can obtain an output which is based on a range of distances located in two adjacent columns ?

EG:

Increasing distances as we go down the page -

Cell A1 = 5.0 & cell B1 = 5.5 / Need an output to show that these values lay between location "A" and "B"
Cell A2 = 6.2 & cell B2 = 7.8 / Need an output to show that these values lay between location "C" and "D"
Cell A3 = 9.5 & cell B3 = 9.8 / Need an output to show that these values lay between location "E" and "F"

...... and so on.

I'm assuming that the answer lays somewhere in a VLOOKUP ?

Thanks

Steve

Sorry forgot to mention, for the example -

Location "A" to "B" would be "A" = 5.0 and "B" would be = 6.0 (Range 1).
Location "C" to "D" would be "C" = 6.1 and "D" would be = 8.0 (Range 2).
Location "E" to "F" would be "E" = 8.1 and "F" would be = 10.0 (Range 3).

Hi!

If I understand correctly what you want, maybe the suggestion below can helps:

In C2 and copy down

=IF(ISERROR(1/(VLOOKUP(A2,$E$2:$E$5,1)=VLOOKUP(B2,$E$2:$E$5,1))),
"No Range",VLOOKUP(A2,$E$2:$G$5,3))

Or

=IF(ISERROR(1/(LOOKUP(A2,$E$2:$E$5)=LOOKUP(B2,$E$2:$E$5))),
"No Range",LOOKUP(A2,$E$2:$G$5))

	A	B	C	D	E	F	G	H	I
1	Start	End	Result		Start	End	Range	Letters
2	5	5,5	Range1		5	6	Range1	A-B
3	6,2	7,8	Range2		6,1	8	Range2	C-D
4	9,5	9,8	Range3		8,1	10	Range3	E-F
5	9,9	10	Range3		10,1		No Range
6	10	11	No Range
7	4	5	No Range
8	5	7	No Range
9
***	******	*****	*********	**	******	******	*********	*******	**

<tbody>
</tbody>

Markmzz