ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Finding a number within a range of numbers (https://www.excelbanter.com/excel-worksheet-functions/244939-finding-number-within-range-numbers.html)

ukbandit

Finding a number within a range of numbers
 
(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


--
All help is greatl appreciated and bless those with the knowledge

Jacob Skaria

Finding a number within a range of numbers
 
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 FormatConditional Formatting
3. For Condition1Select '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 ButtonPattern and select your color (say Red)
5. Hit OK


If this post helps click Yes
---------------
Jacob Skaria


"ukbandit" wrote:

(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


--
All help is greatl appreciated and bless those with the knowledge


ukbandit

Finding a number within a range of numbers
 
Many thanks Jacob. Of all the things I tried, I did not think of that one.
Much appreciated.

ukbandit
--
All help is greatly appreciated and bless those with the knowledge


"Jacob Skaria" wrote:

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 FormatConditional Formatting
3. For Condition1Select '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 ButtonPattern and select your color (say Red)
5. Hit OK


If this post helps click Yes
---------------
Jacob Skaria


"ukbandit" wrote:

(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


--
All help is greatl appreciated and bless those with the knowledge



All times are GMT +1. The time now is 11:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com