Help with IF/AND statement

S

shwndfrnkln

New Member
Joined
Jul 2, 2012
Messages
5
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>

Spreadsheet Formulas
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.
Sort by date Sort by votes
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")
 
Upvote 0
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
 
Upvote 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.
 
Upvote 0
With the above formula, you will see 5 trues. If E1,F1,G1....etc are indeed model numbers
 
Upvote 0
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

ABCDEFGHIJKLMNOP
1Part #Model # 1T13351T18241T18726T18826Y38886Y49106Y58008E47358E70089W15629W15649W6273
212871151285302 1287115TrueTrueTrueFalseTrueFalseTrueTrueTrueTrueTrueTrue
312873481457108 1287348TrueTrueTrueFalseTrueFalseTrueTrueTrueTrueTrueTrue
412873481880669 1355561TrueTrueTrueFalseTrueFalse
512873482975421 1644220TrueTrueTrueFalseTrueFalse
612873483570699 1666323TrueTrueTrueFalseTrueFalse

<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>

Spreadsheet Formulas
CellFormula
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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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

ABCDEFGHIJKLMN
1Part #Model # 1285302145710818806692975421357069935707041355569210313724517352451736
212871151285302 1287115TrueFalseFalseFalseFalseFalseFalseFalseFalseFalse
312873481457108 1287348FalseTrueFalseFalseFalseFalseFalseFalseFalseFalse
412873481880669 1355561TrueTrueTrueTrueTrueTrue
512873482975421 1644220TrueTrueTrueTrueTrueTrue
612873483570699 1666323
712873483570704 1695817
813555611355569 1741406
913555612103137 2464384
1013555612451735 2514324
1113555612451736 2609555
1213555612501912 2833534
1313555613351063 3154128
1416442201355569 3707902
1516442202451735 3716001
1616442202451736 3717046
1716442202501912 3727386
1816442203351063 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>

Spreadsheet Formulas
CellFormula
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
 
Upvote 0
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")
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Help with Combination of VLookUp and SumIfs
Replies
9
Views
125
Fluff
Fluff
H
  • Question
Excel Table Representation Query: 2 columns in the data set with dates which needs to be aligned to get right result.
Replies
1
Views
245
GraH
GraH
T
  • Question
Help with VBA Code
Replies
1
Views
241
NdNoviceHlp
NdNoviceHlp
E
  • Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
1K
profray
P
O
  • Question
Find in range with multiple criteria and if no match, look in another range -- VBA
Replies
0
Views
301
ouvay
O

Forum statistics

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

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top