Can I do a IF statement that if true, it completes another formula?

Assuming you have 365 then maybe use FILTER so something like this maybe:
=LET(InRange, Sheet2!A:F,
OrderRows, FILTER(InRange, INDEX(InRange,,1)=A2,""),
FindSpecials, FILTER(OrderRows, SUM(--(INDEX(OrderRows,,5)={"Apples","Oranges","Bananas"})),""),
INDEX(FindSpecials, 1, 6))
so LET() allows you to make and use 'local' variables. So this first line sets an input range, then filters based on the 1st column of that range for order number A2, then filters those results based on the 5th column for any of those special terms, then outputs the 6th column. of the first one found (remover the "1" to have a full list of results 'spill')
alternatively in a shorter statement you can combine those steps:
=LET(in, Sheet2!A:F,
INDEX(FILTER(in, (INDEX(in,,1)=A2)*SUM(--(INDEX(in,,5)={"Apples","Oranges","Bananas"})),""),,6))

Hopefully this at least gives you an idea how to use these newer powerful functions.