# IF and AND formula for multiple criteria

#### biz_kid1

##### New Member
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!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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,"")

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

Replies
1
Views
191
Replies
3
Views
188
Replies
9
Views
205
Replies
10
Views
171
Replies
1
Views
140

1,202,909
Messages
6,052,493
Members
444,587
Latest member
ezza59

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back