Finding a number within a range of numbers

[ukbandit]

(Below) I have 2 columns. Column 1, row 1 contains the minimum and column 2,
row 1 contains the Maximum. I want to be able to enter a number (say 55) and
it will search for that number within these ranges and highlight it with
either text or a colour (color).
I would prefer it to be simple (ie.a formula). Would appreciate your help as
it has got me baffled. Thanks.

1 50
51 100
101 150
151 160

Enter Number: 55

 

[Jacob Skaria]

Try the below...

--Arrange data as seen below. You range is A1:B4
--You are entering the query number in cell c1.

Col A Col B Col C
1 50 55
51 100
101 150
151 160

1. Select the cell/Range (say A1:B4).
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=AND($A1<=$C$1,$B1>=$C$1)

Please note that the cell reference A1 mentioned in the formula is the
active cell in the selection. Active cell will have a white background even
after selection
4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK


If this post helps click Yes

 

[ukbandit]

Many thanks Jacob. Of all the things I tried, I did not think of that one.
Much appreciated.

ukbandit