We help IT Professionals succeed at work.

Matching multpile criteria in Excel

on
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
Comment
Watch Question

View Solution Only

Consultant

Commented:
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?

Thanks,

Alan.

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
Consultant

Commented:
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.
Consultant

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

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.

Commented:
Try this for D2, copy down

You don't need the countifs for "C5", as the last criteria is B2="C5"
Consultant
Commented:
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.