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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.