# Formula for the use of multiple IFs and AND statements

#### Toncar58

##### New Member
Hello, for convenience I have uploaded a sheet.
I am looking for help with an appropriate excel formula to solve my dilemma. In the attached worksheet I seek a formula in column E. The conditions are If column D is "Yes" and only if yes and if column C is equal to any of the names in Column F. Then if positive to both conditions then a cell in column G otherwise the cell should be "".

To summarise, looking at row 5 in the attached sheet, I want Cell E5 to reflect "", because D5 = Yes, but C5 is not equal to any cells in column F. If however C5 read "BCA" or any other name in Column F and D5 equals "Yes" then either G5 or G6 would be the answer.

Naturally every row in column E will have the appropriately amended formula.

I hope I have explained myself correctly.

Thanks
Toncar58

#### Attachments

• Screen Shot 2021-02-24 at 15.04.33.png
60.6 KB · Views: 5

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Excel Formula:
``=IF(D5<>"Yes","",IFERROR(INDEX(\$G\$5:\$G\$13,MATCH(C5,\$F\$5:\$F\$13,0)),""))``

#### Toncar58

##### New Member
Thank you for the welcome and suggestion. Will try and get back to you

#### Toncar58

##### New Member
Hi ... didn't quite work. So from the attached image you will see that Column E correctly identifies the three cells that meet the conditions. Where the issue is now, is that Bothe fields being met, if column C says "Strydoms" then column E should be "Strydoms" and similarly if it said BCA then the answer should be BCA. My apologies, for not making that clear before.

#### Attachments

• Screen Shot 2021-02-24 at 15.34.46.png
60.4 KB · Views: 2

#### Fluff

##### MrExcel MVP, Moderator

In that case try
Excel Formula:
``=IF(OR(D5<>"Yes",ISNA(MATCH(C5,\$F\$5:\$F\$13,0))),"",C5)``

#### Toncar58

##### New Member
Almost there, apologies, as Im not being coherent ... E5,E6 and E13 are correct. Just realised that I explained myself incorrectly.
The first condition is column D must be "Yes" .. second condition is C5 must equal one of either G5 or G6. If C5 = G5 and D5 is Yes then the answer should be G5. And if D5 = Yes and C5 = G6 then the answer should be G6.

Feeling rather "dull" for my mistake ... thanks for your patience!

#### Attachments

• Screen Shot 2021-02-24 at 15.57.17.png
67 KB · Views: 2

#### Fluff

##### MrExcel MVP, Moderator

So column F is not used required?

#### Toncar58

##### New Member
Column F is there merely as a drop down for Column C .... apologies

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=IF(OR(D5<>"Yes",ISNA(MATCH(C5,\$G\$5:\$G\$6,0))),"",C5)``

#### Toncar58

##### New Member
Thank you sooooo much ... work perfectly! Once again, thanks for your patience.

#### Attachments

• Screen Shot 2021-02-24 at 16.51.30.png
62.9 KB · Views: 0

Replies
1
Views
71
Replies
12
Views
179
Replies
6
Views
121
Replies
4
Views
162
Replies
1
Views
85

1,129,498
Messages
5,636,677
Members
416,935
Latest member
Atulcp

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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