Returning range when a value is entered (greater than/equal to)

[David2]

I'd like to construct a formula where I would enter a number and the result would return the range in which the number is located. The ranges use greater than/equal conditions.

https://imgur.com/E0A35Xd

I thought about constructing a nesting IF condition , where the condition_if_true_ would return the appropriate range and if_not would go to the next IF condition however I'm open to ideas.

 

[Osvaldo Palmeiro]

With "x" number in A1 try this formula in any empty cell:

=LOOKUP(J3,{0;11;51;101;201},{"x≤10";"10<x≤50";"50<x≤100";"100<x≤200";"x>200"})<x≤50";"50<x≤100";"100<x≤200";"x><x≤50";"50<x≤100";"100<x≤200";"x>
or replacing semicolons by commas
=LOOKUP(J3,{0,11,51,101,201},{"x≤10","10<x≤50","50<x≤100","100<x≤200","x>200"})<x≤50","50<x≤100","100<x≤200","x>

PS - Don't know how but code tags were "eating" part of the formulas
</x≤50","50<x≤100","100<x≤200","x></x≤50";"50<x≤100";"100<x≤200";"x></x≤50";"50<x≤100";"100<x≤200";"x>

 

[Osvaldo Palmeiro]

New try:

=LOOKUP(J3,{0;11;51;101;201},{"x≤10";"10<x≤50";"50<x≤100";"100<x≤200";"x>200"})

=LOOKUP(J3,{0,11,51,101,201},{"x≤10","10<x≤50","50<x≤100","100<x≤200","x>200"})

The problem persists
The second part of the first formula should be ~~~> {"x≤10";"10<x≤50";"50<x≤100";"100<x≤200";"x>200"})
an d for the second ~~~>{"x≤10","10<x≤50","50<x≤100","100<x≤200","x>200"})
</x≤50","50<x≤100","100<x≤200","x></x≤50";"50<x≤100";"100<x≤200";"x>

 

[Aladin Akyurek]

Your range specification doesn't look consistent on that "imgur" you link the forum to.

Maybe:

=LOOKUP(A1,$H$1:$H$5)&" >= X "&LOOKUP(A1,$H$1:$I$5)

 

[markmzz]

I'd like to construct a formula where I would enter a number and the result would return the range in which the number is located. The ranges use greater than/equal conditions.

https://imgur.com/E0A35Xd

I thought about constructing a nesting IF condition , where the condition_if_true_ would return the appropriate range and if_not would go to the next IF condition however I'm open to ideas.

Hi David2,

If possible, you can try this too in B2:

=LOOKUP(A2,B6:B10,A6:A10)

Or

=LOOKUP(A2,{0;10.01;50;100;200},A6:A10)

	A	B	C
1	Enter value	Calculated range
2	0	x<=10
3
4
5	Ranges to display	Values
6	x<=10	0
7	10<x<50< font=""></x<50<>	10,01
8	50<=x<100	50
9	100<=x<200	100
10	x>=200	200
11
***	****************	****************	**

<tbody>
</tbody>

Markmzz

 

[Aladin Akyurek]

Your range specification doesn't look consistent on that "imgur" you link the forum to.

Maybe:

=LOOKUP(A1,$H$1:$H$5)&" >= X "&LOOKUP(A1,$H$1:$I$5)

I see I forgat to inline the table to use...

=LOOKUP(A1,{0;10;50;100;200})&" >= X "&LOOKUP(A1,{0,"< 10";10,"< 50";50,"< 100";100,"< 200";200,"< 9.99E+307"})

 

[David2]

I ended up using markmzz's method and so far it's working great.

Thank you all for your time and ideas.

 

[markmzz]

I ended up using markmzz's method and so far it's working great.

Thank you all for your time and ideas.

You're welcome.

The most important thing is that your problem was solved.

Markmzz