# Get a result based on conditions in several columns

#### charlie637

##### New Member
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)

 A B C D E 1 A1 Bob Yes 2 A1 Pet 17 3 A1 John 4 A2 Mike 17 5 A2 Jen 6 A2 Mary 7 A2 Bob Yes 8 A3 John 9 A3 Jack 10 A3 Lisa

<tbody>
</tbody>

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Chrisdontm

##### Well-known Member
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

##### New Member
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

##### New Member
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

##### Banned user

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

##### Banned user
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

##### Banned user

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:

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.

Last edited:

#### charlie637

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

#### charlie637

##### New Member
Thanks for following up.

#### inactiveuserps07

##### Banned user
Glad it was resolved quickly, Charlie.

Replies
5
Views
165
Replies
2
Views
79
Replies
3
Views
102
Replies
10
Views
160
Replies
3
Views
89

1,136,314
Messages
5,675,021
Members
419,543
Latest member
Casp

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

### Which adblocker are you using?

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

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