Formula for data validation drop down that is dependent on a variable value.

Hi all,
I am trying to add some data validation to a column of cells on the tab RM order. (I think data validation would be the correct solution anyway!).
Column F on the attached file is a list of ingredients, that is populated via drop downs in column C.
This then generates all of the required ingredients, and shows the required quantity, and where the ingredients are, as well as if there is enough.
Now. Some of the ingredients can be substituted for alternatives. These are all on a tab named "Lookup List". Ideally, I would love a formula that checks each ingredient, and if there is not enough, it automatically checks the next on the list and if enough stock, uses that one but I suspect that is rather complicated, so I thought an easier way would be via data validation, so if the stock is zero (not enough), then you can use the drop down and select an alternative for that product.
I have tried to do it via a named range with name manager, but the validation will only work with a specific cell or column, and I have set the named range as a bigger range.
Can anyone help with a formula to resolve this please?

Thank you in advance for any help.