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
pls try
Open in new window
Regards