C4 : =IFERROR(IF(VLOOKUP($B4,$H$3:$I$4,2,FALSE)=C$3,"YES",""),"")
=TRIM(MID(SUBSTITUTE(A1,";",REPT(" ",LEN(A1))),(4)*LEN(A1)+1,LEN(A1)))
=INDEX(TEXTSPLIT(A1,";"),5)
=SWITCH(A1,"Apple",B1+5,"Orange",B1+3)
=SWITCH(A1, "Apple",B1+5, "Orange",B1+3)