IF and AND formula for multiple criteria

[biz_kid1]

Excel 2010

	A	B	C	D	E	F	G
1	Poll Results — Question #1				Enter the correct response in the correct age column by using a formula
2	Key:	1 = Strongly Disagree
3		2 = Disagree
4		3 = Neutral
5		4 = Agree
6		5 = Strongly Agree
7
8	Subject ID	Age	Response	18-34	35-49	50-64	65+
9	1	19	4
10	2	23	5
11	3	38	3
12	4	44	4
13	5	51	2
14	6	20	4
15	7	65	1
16	8	49	4
17	9	60	3
18	10	69	2

<TBODY>
</TBODY>

I need an IF and AND formula to place whatever result is in the response code field in the appropriate age range field.

I tried the following formula:
=IF(B9(>=18 AND <=34),C9,IF(B9(>=35 AND <=49),C9,IF(B9(>=50 AND <=64),C9,IF(B9(>=65),C9))))

I know this is wrong, but it sounded good!

Thanks in advance for your help!

 

[Andrew Poulsom]

In D9 copied down and across:

=IF(AND($B9>=(LEFT(D$8,2)+0),IF(LEN(D$8)>3,$B9<=(RIGHT(D$8,2)+0),TRUE())),$C9,"")

 

[biz_kid1]

Thanks! That was a fantastically FAST and accurate response! Just what I was looking for!