Excel If Statements

I am attempting to create an excel formula to get it to search a row for the word "Yes". If that word appears it is to pull the text from another cell. The formula works if I only have the first two conditions but once I add the third I receive an error message. Is there another way to get this formula to work?

=IF(AND(Cabinets!G2="Yes",Cabinets!G3="Yes",Cabinets!G4="Yes"),Cabinets!B2,Cabinets!B3,Cabinets!B4)

=INDEX(Cabinets!$A:$A,MATCH("Yes",Cabinets!$D:$D,0))