Formula for the use of multiple IFs and AND statements

[Toncar58]

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

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(D5<>"Yes","",IFERROR(INDEX($G$5:$G$13,MATCH(C5,$F$5:$F$13,0)),""))

 

[Toncar58]

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

 

[Toncar58]

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.

 

[Fluff]

In that case try

=IF(OR(D5<>"Yes",ISNA(MATCH(C5,$F$5:$F$13,0))),"",C5)

 

[Toncar58]

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!

 

[Fluff]

So column F is not used required?

 

[Toncar58]

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

 

[Fluff]

Ok how about

=IF(OR(D5<>"Yes",ISNA(MATCH(C5,$G$5:$G$6,0))),"",C5)

 

[Toncar58]

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