Formula for the use of multiple IFs and AND statements

Toncar58

New Member
Joined
Feb 24, 2021
Messages
10
Office Version
  1. 365
Platform
  1. MacOS
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
    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
Joined
Jun 12, 2014
Messages
56,533
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=IF(D5<>"Yes","",IFERROR(INDEX($G$5:$G$13,MATCH(C5,$F$5:$F$13,0)),""))
 

Toncar58

New Member
Joined
Feb 24, 2021
Messages
10
Office Version
  1. 365
Platform
  1. MacOS
Thank you for the welcome and suggestion. Will try and get back to you
 

Toncar58

New Member
Joined
Feb 24, 2021
Messages
10
Office Version
  1. 365
Platform
  1. MacOS
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
    Screen Shot 2021-02-24 at 15.34.46.png
    60.4 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,533
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Toncar58

New Member
Joined
Feb 24, 2021
Messages
10
Office Version
  1. 365
Platform
  1. MacOS
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
    Screen Shot 2021-02-24 at 15.57.17.png
    67 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,533
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So column F is not used required?
 

Toncar58

New Member
Joined
Feb 24, 2021
Messages
10
Office Version
  1. 365
Platform
  1. MacOS
Column F is there merely as a drop down for Column C .... apologies
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,533
Office Version
  1. 365
Platform
  1. Windows
Ok how about
Excel Formula:
=IF(OR(D5<>"Yes",ISNA(MATCH(C5,$G$5:$G$6,0))),"",C5)
 

Toncar58

New Member
Joined
Feb 24, 2021
Messages
10
Office Version
  1. 365
Platform
  1. MacOS
Thank you sooooo much ... work perfectly! Once again, thanks for your patience.
 

Attachments

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

Watch MrExcel Video

Forum statistics

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

Disable AdBlock

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
Top