Get a result based on conditions in several columns

[charlie637]

Trying to solve this.

What I want: Formula in column D saying Yes or No based on following rules:
If a cell in Col. B equals "Bob", then look everywhere in Col. A with the same content, for ex "A1", and if in Col C there is "17", then put "Yes" in D on same row as Bob.

Otherwise, "No", as shown in D6.

If B contains no "Bob", nothing happens (could return "No" or nothing)

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]A1
[/TD]
[TD="align: center"]Bob
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Yes
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]A1
[/TD]
[TD="align: center"]Pet
[/TD]
[TD="align: center"]17
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]A1
[/TD]
[TD="align: center"]John
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]A2
[/TD]
[TD="align: center"]Mike
[/TD]
[TD="align: center"]17
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]A2
[/TD]
[TD="align: center"]Jen
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]A2
[/TD]
[TD="align: center"]Mary
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]A2
[/TD]
[TD="align: center"]Bob
[/TD]
[TD][/TD]
[TD="align: center"]Yes
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]A3
[/TD]
[TD="align: center"]John
[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]A3
[/TD]
[TD="align: center"]Jack
[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"]A3
[/TD]
[TD="align: center"]Lisa
[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Chrisdontm]

I believe I have two formulas to try;

First lets go with the "Bob Only" formula;

Put this in cell: D1 and copy it on down;

=IF(COUNTIF($A:$A,IF($B1="Bob",$A1,""))=COUNTIFS($A:$A,IF($B1="Bob",$A1,""),$C:$C,""),"","Yes")
______________________________________________

The other formula you may want to try is to make it more dynamic;

That is to say, you may want to use the formula with different names.

So use cell: G1 to enter in the person's name you want to use.

Then in cell: D1 enter this and copy it on down;

=IF(COUNTIF($A:$A,IF($B1=$G$1,$A1,""))=COUNTIFS($A:$A,IF($B1=$G$1,$A1,""),$C:$C,"<>"&17),"","Yes")

( With this one you could use another person's name, if you wanted to...)

 

[charlie637]

Thank you so much. Works great!
Now, if instead of just "Bob", I have a list of names that I want to behave like for "Bob", using either a range where all those names would be or by creating a list, how would the formula look like?

 

[charlie637]

One more thing.
In the first formula, I do not think it takes into consideration "17" specifically. But it needs to be. There could be other values in Col. C, so the formula needs to behave the way I described it only if there is that number 17 in Col. C.
Thanks!

 

[inactiveuserps07]

Nice, charlie637 — and quick.

Here is a shorter version:

=IF(B1="Bob",IF(COUNTIFS($A:$A,A1,$C:$C,17)>0, "YES", ""),"")

Or, if input fields for "Bob" and "17 were G1 and J1 respectively:

=IF(B1=$G$1,IF(COUNTIFS($A:$A,A1,$C:$C,$J$1)>0, "YES", ""),"")

 

[inactiveuserps07]

Christdontm, if the names were in Column N, for instance, and the input field where you'd check for 17 was $J$1:


=IF(IFERROR(MATCH(B1,$N:$N,0),0)>0,IF(COUNTIFS($A:$A,A1,$C:$C,$J$1)>0,"YES",""),"")


That would go into D1 and be copied down the column as far as you need it.

 

[inactiveuserps07]

You're always better to have things like the name list formatted as a named table.

So let's say your name list is in N1:N10 with the heading NAME. Select N1:N10, choose Insert from the ribbon and then click "Table." Make sure the checkbox for "My table has headers" is checked. Click "OK." The tab will switch automatically to the Design tab. Rename your list to the left of the ribbon, for instance: NameList1.

Then the formula would look like this:


=IF(IFERROR(MATCH(B1,NameList1,0),0)>0,IF(COUNTIFS($A:$A,A1,$C:$C,$J$1)>0,"YES",""),"")


And if your main data set will always have people's names two columns to the left of your "YES" column, you can use this formula in the "YES" column, no matter where it starts:


=IF(IFERROR(MATCH(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),NameList1,0),0)>0,IF(COUNTIFS($A:$A,A1,$C:$C,$J$1)>0,"YES",""),"")


I'd also recommend formatting your main data as a table the same way I described above, so that the formula will automatically be added as you add new records. Otherwise, you'll have to keep copying it down to the new entries.

 

[charlie637]

It works great! Thank you all so much for your help. Very much appreciated!

 

[charlie637]

I actually already did what you suggested, creating a list instead
Thanks for following up.

 

[inactiveuserps07]

Glad it was resolved quickly, Charlie.