I would like a formula to do the following;
Formula to feature in cell G2
IF cell E2 contains either of these three values, Pending
, Awaiting supplier confirmation
, SBA sent | Awaiting return
, then return an email address found in the range K2:L35.
The email address must correspond to the name found in cell A2. If no value is present in A2 then return a blank.
If cell E2 contains any value other than the three stated above then also return a blank
This formula will be copied down for each row