Help with IF/AND statement

shwndfrnkln

New Member
I have an if/and statement that is checking 2 data points and returning a true or false. Column A is the part number and Column B is the model number. I need to return a true statement if the part number falls under a model number. This works but only for the first model number listed. How do I get it to continue past the first model number and return true instead of false statements?

Any help is very appreciated.

Zone 1 Matrix

 A B C D E F G H I J K L 1 Part # Model # 1T1335 1T1824 1T1872 6T1882 6Y3888 6Y4910 6Y5800 8E4735 2 1287115 1285302 1287115 False False False False False False False False 3 1287348 1457108 1287348 False False False False False False False False 4 1287348 1880669 1355561 5 1287348 2975421 1644220 6 1287348 3570699 1666323 7 1287348 3570704 1695817 8 1355561 1355569 1741406 9 1355561 2103137 2464384 10 1355561 2451735 2514324 11 1355561 2451736 2609555 12 1355561 2501912 2833534 13 1355561 3351063 3154128 14 1644220 1355569 3707902 15 1644220 2451735 3716001

<TBODY>
</TBODY>

 Cell Formula E2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,E1=\$B\$2:\$B\$136),"True","False") F2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,F1=\$B\$2:\$B\$136),"True","False") G2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,G1=\$B\$2:\$B\$136),"True","False") H2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,H1=\$B\$2:\$B\$136),"True","False") I2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,I1=\$B\$2:\$B\$136),"True","False") J2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,J1=\$B\$2:\$B\$136),"True","False") K2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,K1=\$B\$2:\$B\$136),"True","False") L2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,L1=\$B\$2:\$B\$136),"True","False") E3 =IF(AND(\$D\$3=\$A\$2:\$A\$136,E1=\$B\$2:\$B\$136),"True","False") F3 =IF(AND(\$D\$3=\$A\$2:\$A\$136,F1=\$B\$2:\$B\$136),"True","False") G3 =IF(AND(\$D\$3=\$A\$2:\$A\$136,G1=\$B\$2:\$B\$136),"True","False") H3 =IF(AND(\$D\$3=\$A\$2:\$A\$136,H1=\$B\$2:\$B\$136),"True","False") I3 =IF(AND(\$D\$3=\$A\$2:\$A\$136,I1=\$B\$2:\$B\$136),"True","False") J3 =IF(AND(\$D\$3=\$A\$2:\$A\$136,J1=\$B\$2:\$B\$136),"True","False") K3 =IF(AND(\$D\$3=\$A\$2:\$A\$136,K1=\$B\$2:\$B\$136),"True","False") L3 =IF(AND(\$D\$3=\$A\$2:\$A\$136,L1=\$B\$2:\$B\$136),"True","False")

<TBODY>
</TBODY>

<TBODY>
</TBODY>

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
From what I can tell, you're trying to check to see: For each part# in D check if it exists in the list (col A) and then check to see if the model # in row 1 exists in (col B)?
I'm not sure though.

If true, I'd use this in cell E2 (then copy it across and down):
Code:
``=IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D2)>0,COUNTIF(\$B\$2:\$B\$136,E\$1)>0),"True","False")``

If the formula works for you do not need IF statment
=AND(COUNTIF(\$A\$2:\$A\$136,\$D2)>0,COUNTIF(\$B\$2:\$B\$136,E\$1)>0)
is enough.
You can simplify that to
=COUNTIFS(\$A\$2:\$A\$136,\$D2,\$B\$2:\$B\$136,E\$1)>0
or for Excel 03
=SUMPRODUCT(--(\$A\$2:\$A\$136=\$D2),--(\$B\$2:\$B\$136=E\$1))>0

If the part number in column A is there and has a model number in column B then I need a true statement in my matrix at the right. If you look at part # 1287348 in column A it goes into 5 model #'s in column B. So to the right I should see true for these 5 model numbers and false for everything else, but I only get a true with the first model number and everything else is false with my statment.

With the above formula, you will see 5 trues. If E1,F1,G1....etc are indeed model numbers

Jaybee3, thanks for the help.

I have copied your statment and below is the result. I am getting trues where I should not be.

Zone 1 Matrix

 A B C D E F G H I J K L M N O P 1 Part # Model # 1T1335 1T1824 1T1872 6T1882 6Y3888 6Y4910 6Y5800 8E4735 8E7008 9W1562 9W1564 9W6273 2 1287115 1285302 1287115 True True True False True False True True True True True True 3 1287348 1457108 1287348 True True True False True False True True True True True True 4 1287348 1880669 1355561 True True True False True False 5 1287348 2975421 1644220 True True True False True False 6 1287348 3570699 1666323 True True True False True False

<COLGROUP> <COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 56px"><COL style="WIDTH: 130px"><COL style="WIDTH: 10px"><COL style="WIDTH: 64px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"> </COLGROUP><TBODY>
</TBODY>

 Cell Formula E2 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D2)>0,COUNTIF(\$B\$2:\$B\$136,E\$1)>0),"True","False") F2 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D2)>0,COUNTIF(\$B\$2:\$B\$136,F\$1)>0),"True","False") G2 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D2)>0,COUNTIF(\$B\$2:\$B\$136,G\$1)>0),"True","False") H2 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D2)>0,COUNTIF(\$B\$2:\$B\$136,H\$1)>0),"True","False") I2 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D2)>0,COUNTIF(\$B\$2:\$B\$136,I\$1)>0),"True","False") J2 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D2)>0,COUNTIF(\$B\$2:\$B\$136,J\$1)>0),"True","False") K2 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D2)>0,COUNTIF(\$B\$2:\$B\$136,K\$1)>0),"True","False") L2 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D2)>0,COUNTIF(\$B\$2:\$B\$136,L\$1)>0),"True","False") M2 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D2)>0,COUNTIF(\$B\$2:\$B\$136,M\$1)>0),"True","False") N2 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D2)>0,COUNTIF(\$B\$2:\$B\$136,N\$1)>0),"True","False") O2 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D2)>0,COUNTIF(\$B\$2:\$B\$136,O\$1)>0),"True","False") P2 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D2)>0,COUNTIF(\$B\$2:\$B\$136,P\$1)>0),"True","False") E3 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D3)>0,COUNTIF(\$B\$2:\$B\$136,E\$1)>0),"True","False") F3 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D3)>0,COUNTIF(\$B\$2:\$B\$136,F\$1)>0),"True","False") G3 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D3)>0,COUNTIF(\$B\$2:\$B\$136,G\$1)>0),"True","False") H3 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D3)>0,COUNTIF(\$B\$2:\$B\$136,H\$1)>0),"True","False") I3 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D3)>0,COUNTIF(\$B\$2:\$B\$136,I\$1)>0),"True","False") J3 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D3)>0,COUNTIF(\$B\$2:\$B\$136,J\$1)>0),"True","False") K3 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D3)>0,COUNTIF(\$B\$2:\$B\$136,K\$1)>0),"True","False") L3 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D3)>0,COUNTIF(\$B\$2:\$B\$136,L\$1)>0),"True","False") M3 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D3)>0,COUNTIF(\$B\$2:\$B\$136,M\$1)>0),"True","False") N3 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D3)>0,COUNTIF(\$B\$2:\$B\$136,N\$1)>0),"True","False") O3 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D3)>0,COUNTIF(\$B\$2:\$B\$136,O\$1)>0),"True","False") P3 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D3)>0,COUNTIF(\$B\$2:\$B\$136,P\$1)>0),"True","False") E4 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D4)>0,COUNTIF(\$B\$2:\$B\$136,E\$1)>0),"True","False") F4 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D4)>0,COUNTIF(\$B\$2:\$B\$136,F\$1)>0),"True","False") G4 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D4)>0,COUNTIF(\$B\$2:\$B\$136,G\$1)>0),"True","False") H4 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D4)>0,COUNTIF(\$B\$2:\$B\$136,H\$1)>0),"True","False") I4 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D4)>0,COUNTIF(\$B\$2:\$B\$136,I\$1)>0),"True","False") J4 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D4)>0,COUNTIF(\$B\$2:\$B\$136,J\$1)>0),"True","False") E5 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D5)>0,COUNTIF(\$B\$2:\$B\$136,E\$1)>0),"True","False") F5 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D5)>0,COUNTIF(\$B\$2:\$B\$136,F\$1)>0),"True","False") G5 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D5)>0,COUNTIF(\$B\$2:\$B\$136,G\$1)>0),"True","False") H5 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D5)>0,COUNTIF(\$B\$2:\$B\$136,H\$1)>0),"True","False") I5 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D5)>0,COUNTIF(\$B\$2:\$B\$136,I\$1)>0),"True","False") J5 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D5)>0,COUNTIF(\$B\$2:\$B\$136,J\$1)>0),"True","False") E6 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D6)>0,COUNTIF(\$B\$2:\$B\$136,E\$1)>0),"True","False") F6 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D6)>0,COUNTIF(\$B\$2:\$B\$136,F\$1)>0),"True","False") G6 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D6)>0,COUNTIF(\$B\$2:\$B\$136,G\$1)>0),"True","False") H6 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D6)>0,COUNTIF(\$B\$2:\$B\$136,H\$1)>0),"True","False") I6 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D6)>0,COUNTIF(\$B\$2:\$B\$136,I\$1)>0),"True","False") J6 =IF(AND(COUNTIF(\$A\$2:\$A\$136,\$D6)>0,COUNTIF(\$B\$2:\$B\$136,J\$1)>0),"True","False")

<TBODY>
</TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

You are not giving proper information.
Is your data there are no two matching examples(top row E1 onwards) so can we establish what's TRUE/FALSE?

Ah right I think I know what he wants now.:

E2 should check for existence of 1287115 1T1335, E3 should check for: 1287115 1T1824...etc.

Is that what you want?

Sorry for the confusion. My top row goes out to AR on Model #s. I have sliced it down to 18 rows for better clarity.

My data is in columns A and B. Part #s are not unique but Model #s are unique.

I have a matrix at the right with Part # in column D and Model # in row 1.

This is my statment =IF(AND(\$D\$2=\$A\$2:\$A\$136,E1=\$B\$2:\$B\$136),"True","False").

Part #s go into Model #s. So if a part number is in column A and has a Model # in column B put true in the field. If not put false.
Row 1 and 2 have my statment reflected. As you can see I get the first True Model # then it places False for everything else.
Look at 1287348 in column D, given the data in column A and B, column F3,G3,H3,I3, and J3 should have True reflected but it stops at just the first find.

Rows 4 through 18 have jaybee3's statment.

I hope this is more clear. Again I appoligize for the confusion and appreciate the help.

Sheet1

 A B C D E F G H I J K L M N 1 Part # Model # 1285302 1457108 1880669 2975421 3570699 3570704 1355569 2103137 2451735 2451736 2 1287115 1285302 1287115 True False False False False False False False False False 3 1287348 1457108 1287348 False True False False False False False False False False 4 1287348 1880669 1355561 True True True True True True 5 1287348 2975421 1644220 True True True True True True 6 1287348 3570699 1666323 7 1287348 3570704 1695817 8 1355561 1355569 1741406 9 1355561 2103137 2464384 10 1355561 2451735 2514324 11 1355561 2451736 2609555 12 1355561 2501912 2833534 13 1355561 3351063 3154128 14 1644220 1355569 3707902 15 1644220 2451735 3716001 16 1644220 2451736 3717046 17 1644220 2501912 3727386 18 1644220 3351063 3766550

<COLGROUP> <COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 11px"><COL style="WIDTH: 64px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"><COL style="WIDTH: 71px"><COL style="WIDTH: 65px"><COL style="WIDTH: 65px"> </COLGROUP><TBODY>
</TBODY>

 Cell Formula E2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,E1=\$B\$2:\$B\$136),"True","False") F2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,F1=\$B\$2:\$B\$136),"True","False") G2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,G1=\$B\$2:\$B\$136),"True","False") H2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,H1=\$B\$2:\$B\$136),"True","False") I2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,I1=\$B\$2:\$B\$136),"True","False") J2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,J1=\$B\$2:\$B\$136),"True","False") K2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,K1=\$B\$2:\$B\$136),"True","False") L2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,L1=\$B\$2:\$B\$136),"True","False") M2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,M1=\$B\$2:\$B\$136),"True","False") N2 =IF(AND(\$D\$2=\$A\$2:\$A\$136,N1=\$B\$2:\$B\$136),"True","False") E3 =IF(AND(\$D\$3=\$A\$2:\$A\$18,E1=\$B\$2:\$B\$18),"True","False") F3 =IF(AND(\$D\$3=\$A\$2:\$A\$18,F1=\$B\$2:\$B\$18),"True","False") G3 =IF(AND(\$D\$3=\$A\$2:\$A\$18,G1=\$B\$2:\$B\$18),"True","False") H3 =IF(AND(\$D\$3=\$A\$2:\$A\$18,H1=\$B\$2:\$B\$18),"True","False") I3 =IF(AND(\$D\$3=\$A\$2:\$A\$18,I1=\$B\$2:\$B\$18),"True","False") J3 =IF(AND(\$D\$3=\$A\$2:\$A\$18,J1=\$B\$2:\$B\$18),"True","False") K3 =IF(AND(\$D\$3=\$A\$2:\$A\$18,K1=\$B\$2:\$B\$18),"True","False") L3 =IF(AND(\$D\$3=\$A\$2:\$A\$18,L1=\$B\$2:\$B\$18),"True","False") M3 =IF(AND(\$D\$3=\$A\$2:\$A\$18,M1=\$B\$2:\$B\$18),"True","False") N3 =IF(AND(\$D\$3=\$A\$2:\$A\$18,N1=\$B\$2:\$B\$18),"True","False") E4 =IF(AND(COUNTIF(\$A\$2:\$A\$18,\$D4)>0,COUNTIF(\$B\$2:\$B\$18,E\$1)>0),"True","False") F4 =IF(AND(COUNTIF(\$A\$2:\$A\$18,\$D4)>0,COUNTIF(\$B\$2:\$B\$18,F\$1)>0),"True","False") G4 =IF(AND(COUNTIF(\$A\$2:\$A\$18,\$D4)>0,COUNTIF(\$B\$2:\$B\$18,G\$1)>0),"True","False") H4 =IF(AND(COUNTIF(\$A\$2:\$A\$18,\$D4)>0,COUNTIF(\$B\$2:\$B\$18,H\$1)>0),"True","False") I4 =IF(AND(COUNTIF(\$A\$2:\$A\$18,\$D4)>0,COUNTIF(\$B\$2:\$B\$18,I\$1)>0),"True","False") J4 =IF(AND(COUNTIF(\$A\$2:\$A\$18,\$D4)>0,COUNTIF(\$B\$2:\$B\$18,J\$1)>0),"True","False") E5 =IF(AND(COUNTIF(\$A\$2:\$A\$18,\$D5)>0,COUNTIF(\$B\$2:\$B\$18,E\$1)>0),"True","False") F5 =IF(AND(COUNTIF(\$A\$2:\$A\$18,\$D5)>0,COUNTIF(\$B\$2:\$B\$18,F\$1)>0),"True","False") G5 =IF(AND(COUNTIF(\$A\$2:\$A\$18,\$D5)>0,COUNTIF(\$B\$2:\$B\$18,G\$1)>0),"True","False") H5 =IF(AND(COUNTIF(\$A\$2:\$A\$18,\$D5)>0,COUNTIF(\$B\$2:\$B\$18,H\$1)>0),"True","False") I5 =IF(AND(COUNTIF(\$A\$2:\$A\$18,\$D5)>0,COUNTIF(\$B\$2:\$B\$18,I\$1)>0),"True","False") J5 =IF(AND(COUNTIF(\$A\$2:\$A\$18,\$D5)>0,COUNTIF(\$B\$2:\$B\$18,J\$1)>0),"True","False")

<TBODY>
</TBODY>

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

Easiest way would be to put a key in col C (maybe
Code:
``=A2&"-"&B2``
), then in E2 put:
Code:
``=IF(ISNA(VLOOKUP(\$D2&"-"&E\$1,\$C:\$C,1,FALSE)),"False","True")``

Replies
9
Views
125
Replies
1
Views
245
Replies
1
Views
241
Replies
3
Views
1K
Replies
0
Views
301

1,203,607
Messages
6,056,285
Members
444,855
Latest member

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.

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