Excel 2007 Problem with an IF statement . Help

[Stephen Jon]

Hi Folks, I'd appreciate any help and many thanks in advance for taking the time.

I've excel 2007 and I'm trying to built a table . 

column A will contain a number of random figures from -1 to 100.

The table starting in column C through to column AB will run  parallel 

and each column being a category  1, 2-5, 6-10, 11-20, 21-30 and so on 

below is the formula that when placed  in C2 will show the result of 1 if 1 is in that cell

=IF(ISNUMBER(SEARCH("1",A2)),A2,"")

the problem I have is how do I formulate code where i need  a range from 2-5 ?

so if 3.3 turns up in cell A2 3.3 will show in the category 1-5

I can only do single digits and I need to be able to cover a range of numbers and those in between up to 2 decimal place

[#2]

I hope I've explained it correctly , if not please message so I can help clarify

[#3]

"each column being a category 1, 2-5, 6-10, 11-20, 21-30 and so on"

vs

"so if 3.3 turns up in cell A2 3.3 will show in the category 1-5"

Is "1" a category, or "1-5" is a category?

[#4]

Column A   blank B.   1 C.    1-5 D .  5-10 E.  10-20 F.   20-30 G.    30-40 H.   

      3.5                                      3.5

      1                              1

      10.7                                                 10.7

      2.4                                      2.4

      22.3                                                                                22.3

      35.6                                                                                                   35.6

Above is how I want it to look . 

Column A are random numbers anything from -1 up to 100

As the random numbers are inputted to column A , it automatically appears in the table under the right category and in line with the relevant data input on the same line .

I hope that helps 

[#5]

Column C

=IF($A2 = $C$1, $A2, "")

Column D to last Column

=IF(AND($A2>VALUE(LEFT(D$1, FIND("-", D$1)-1)), $A2<=VALUE(MID(D$1, FIND("-", D$1)+1, 255))), $A2, "")

[#6]

Hi Willy thank you I really appreciate the help .

For Column C  nothing appears with that code

For the other columns hashtag value! appears . sorry I don't have a hashtag symbol

Regards 

Stephen

[#7]

Select C1, Create a name in name manager, Header

=IFERROR(LEFT(C$1, FIND(" ", C$1) - 1), C$1)

 Column C

=IF($A2 = VALUE(Header), $A2, "")

Column D to last column

=IF(AND($A2>VALUE(LEFT(Header, FIND("-", Header)-1)), $A2<=VALUE(MID(Header, FIND("-", Header)+1, 10))), $A2, "")

[#8]

Hi Willy, 

 Sorry to get back to you at such late notice, I've been working away from home, and not had the time to take a look at this formula.

I've tried to insert where I think you  want me place each code . but It doesn't seem to working. I think may be Its me being a  novice. 

So I wander if you where able to screen shot each step that would help me get it right. 

I'm not sure if you can post them here, or send to my email. St3ph3n31@hotmail.com

 I'd be eternally grateful and maybe, be able to return favour the one day. 

Regards 

Stephen