Matching multpile criteria in Excel

Experts,

I have a spreadsheet that multiple match criteria has to be done.  How can I do this

I want to put the value "ADJ"  in column D when:

ID is the same
The ID has a C1 and C5 proc
proc of c1 and c5 are equal

I tried with countifs, but I am missing the piece that would match the respective values in column C when all else is true

In this case line  16 meets the criteria
Line 16 is false because the value of C8 does not match the value of C9 even though all else is true.
Match.xls
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
 
AlanConsultantCommented:
Hi,

morinia:

Sorry again,

Line 16 is  true.  Line 16 is the only line that should have "ADJ" in column D.

Line 8 and 9 when the formula is complete will not have "ADJ" in column D because C8 does not match C9.

The relevance of C8 not equaling C9 is because the formula is not complete.  The part I am having trouble coding is comparing the value of  column c in the row with "C1" in column b to column C in the row with "C5" in column B.

All of the criteria must be met for "ADJ" to be in column "D"

This is my formula but it does not do the complete job.  


I am looking for help to code the formula that meets all the criteria.


=IF(AND(COUNTIFS(A:A,A2,B:B,{"=C1"})>0,COUNTIFS(A:A,A2,B:B,{"=C5"})>0,B2="c5"),"ADJ","")

Okay - No problem.

In that case, did the version I posted above do the job?

If not, where is it wrong?

Thanks,

Alan.
0
 
AlanConsultantCommented:
Hi,

I'm not really understanding your requirement.

Are you saying that D9 = "ADJ" is wrong?

Are you saying that D16 = "ADJ" is wrong?  You state that line 16 meets the critieria, then you go on to say that line 16 is (or should be) false?

Please can you explain further.

Thanks,

Alan.
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
Sorry for the confusion,

The "ADJ"  in columns D9 and D16 are a result of the formula I started which is incomplete.  It does not have the match criteria for  column C.

When the formula is complete the only place "ADJ" should be is on Line 16.  I put this in column E16 in red.


You will see my formula I started in Columns D2 - D16
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
AlanConsultantCommented:
Okay, so please can you expand on what you meant above when you say that:

'Line 16 is false because the value of C8 does not match the value of C9 even though all else is true. '

This is the bit that I don't understand - what is the relevance of C8 not equalling C9 to row 16?

Thanks,

Alan.
0
 
AlanConsultantCommented:
Bit of a shot in the dark subject to the answer to my query above, but is the attached what you mean?

Alan.
Match---Version-1.xls
0
 
moriniaAdvanced Analytics AnalystAuthor Commented:
Sorry again,

Line 16 is  true.  Line 16 is the only line that should have "ADJ" in column D.

Line 8 and 9 when the formula is complete will not have "ADJ" in column D because C8 does not match C9.

The relevance of C8 not equaling C9 is because the formula is not complete.  The part I am having trouble coding is comparing the value of  column c in the row with "C1" in column b to column C in the row with "C5" in column B.

All of the criteria must be met for "ADJ" to be in column "D"

This is my formula but it does not do the complete job.  


I am looking for help to code the formula that meets all the criteria.


=IF(AND(COUNTIFS(A:A,A2,B:B,{"=C1"})>0,COUNTIFS(A:A,A2,B:B,{"=C5"})>0,B2="c5"),"ADJ","")
0
 
Ejgil HedegaardCommented:
Try this for D2, copy down
=IF(AND(COUNTIFS(A:A,A2,B:B,"C1")>0,COUNTIFS(A:A,A2,C:C,C2)>1,B2="C5"),"ADJ","")

You don't need the countifs for "C5", as the last criteria is B2="C5"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.